Menu
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.
479
Because our Epoch time is specified in milliseconds, we may convert it to seconds. To convert milliseconds to seconds, first, divide the millisecond count by 1000. Later, we use DATEADD() to add the number of seconds since the epoch, which is January 1, 1970 and cast the result to retrieve the date since the epoch.
Use the STRFTIME() function to format date\time\datetime data in SQLite. This function takes two arguments. The first argument is a format string containing the date/time part pattern. In our example, we use the format string '%d/%m/%Y, %H:%M'.
So, Epoch data is basically a BIGINT number which defines the number of seconds elapsed between the specified DateTime and 1st January 1970 at 00:00:00.
To change the values in the database, use the UPDATE command:
UPDATE MyTable SET MyColumn = datetime(MyColumn, 'unixepoch', 'localtime') 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.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
