56

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.

Martin Zeitler
  • 1
  • 19
  • 155
  • 216
Phong Tran
  • 561
  • 1
  • 4
  • 4

3 Answers3

37

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

UPDATE MyTable SET MyColumn = datetime(MyColumn, 'unixepoch', 'localtime')
CL.
  • 173,858
  • 17
  • 217
  • 259
  • 1
    Problem solved. I had to convert sqlite db to mysql. In mysql i have more advanced query: `UPDATE yourtable SET yourcolumn = from_unixtime(yourcolumn)` – Phong Tran Feb 01 '13 at 03:15
  • 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. – Roland Jun 29 '23 at 05:44
37

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.

Martin Zeitler
  • 1
  • 19
  • 155
  • 216
5

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

chandrareddy gangireddy
  • 67
  • 1
  • 1