SQLite3 on windows: Convert epoch to normal time

I am trying to convert the following timestamp(in milliseconds since epoch) to normal date-time. Am using sqlite3 on windows xp.

I am using this query: select datetime((timestamp/86400000)+25569) from table;

(timestamp is the column name which contains the values like 1289325613669,1289325823860, 1289327180545).

I dont seem to be getting the right values. Am i doing something wrong?

Answers


Do this:

select datetime('1289325613', 'unixepoch');

The unixepoch modifier expects a value in seconds.

Currently, what you provide to datetime is interpreted as a Julian Day number.

The reference for date and time functions is here


Need Your Help

use sql server .mdf file

sql vb.net project mdf

I am developing a software with vb.net 2010 and sql server 2008 R2. While developing with my laptop where sql server is running every thing is ok, but when I want to deliver the project to the cust...

Change password for Parse.com Android (Front-end check)

java android parse.com passwords

I'm trying to produce a password check before submitting my form, but I'm not getting the desired behavior. Basically no matter what the user inputs for either field, it will submit the new passwor...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.