How to convert Unix epoch time in SQLite

Question

Could you help me convert UNIX epoch time into format yyyy-mm-dd hh:mm:ss (24h) in SQLite? (GMT+7 would be appreciated).

Example: from 1319017136629 to Wednesday, October 19, 2011 4:38:56 PM GMT+7.

p/s: Just looked around and found a solution:

SELECT datetime(1319017136629, 'unixepoch', 'localtime');

But i am still looking for a way to batch convert UNIX epoch time in SQLite.

Seif

Store in the database in UTC, display to the user in their local time. To do anything else is just asking for trouble – Bathhouse

@Petesh how is Epoch timestamp aka Unix time "asking for trouble"? – Beefwood

Answer 1

1319017136629 is a value in milliseconds, which is not UNIX epoch time; therefore it has to be divided by 1000 and rounded to integer seconds; only then DATE() and DATETIME() will convert.

SELECT DATETIME(ROUND(dateColumn / 1000), 'unixepoch') AS isodate FROM tableName

Converting database values to localtime is unfortunate; conversion on output is less problematic.

The Y2038 problem might already be worth a consideration.

Bohlin

Answer 2

To change the values in the database, use the UPDATE command:

UPDATE MyTable SET MyColumn = datetime(MyColumn, 'unixepoch', 'localtime')

Diluent

Problem solved. I had to convert sqlite db to mysql. In mysql i have more advanced query: UPDATE yourtable SET yourcolumn = from_unixtime(yourcolumn) – Seif

Remember to NOT directly convert INT (for unix timestamps) to DATETIME, you have to first change it to VARCHAR, convert it with above UPDATE statement and then convert it to DATETIME. Alternative is to add another column of DATETIME, convert old -> new column directly, delete old column and rename new back to old column. – Tiaratibbetts

Answer 3

Same as stated above:

SELECT DATE(ROUND(1601272453000 / 1000), 'unixepoch')

Hoke

Can you explain? – Jun