CODEWITHSUNDEEP
sqlite
sprezzatura
sprezzatura

Reputation: 472

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?

Upvotes: 2

Views: 4872

Answers (1)

Benoit
Benoit

Reputation: 79175

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

Upvotes: 8

Related Questions