reading unix timestamp with sqlite3

Question

In an sqlite3 database, I am storing date and time as unix timestamps. Examples of unix timestamp we can find on our database: 1457600307000, 1457600109000, 1457599991000.

When executing the following query:

SELECT datetime(my_date, 'unixepoch') as last_update 
FROM my_table;

the results are not what we expect. the date we are getting from executing that query is: -1413-03-01 13:07:12

when we would actually expect a date and time from March / April 2016.

Is there anyway I can sqlite3 to read the timestamps correctly?

Lukasz Szozda · Accepted Answer · 2016-04-09 16:57:47Z

There is difference between number of seconds from start of epoch and number of miliseconds. Divide by 1000:

SELECT my_date, datetime(my_date/1000, 'unixepoch') as last_update 
FROM my_table;

SqlFiddleDemo

Output:

╔════════════════╦═════════════════════╗
║    my_date     ║     last_update     ║
╠════════════════╬═════════════════════╣
║ 1457600307000  ║ 2016-03-10 08:58:27 ║
║ 1457600109000  ║ 2016-03-10 08:55:09 ║
║ 1457599991000  ║ 2016-03-10 08:53:11 ║
╚════════════════╩═════════════════════╝

when we would actually expect a date and time from March / April 2016.

It looks like it is in range.

Collectives™ on Stack Overflow

reading unix timestamp with sqlite3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related