How to convert Unix epoch time in SQLite

epoch, sql, sqlite, unix

Solution

`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.

Problem

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.

Original source