How to convert Unix epoch time in SQLite
NickName:Phong Tran Ask DateTime:2013-01-31T23:33:04

How to convert Unix epoch time in SQLite

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.

Copyright Notice：Content Author:「Phong Tran」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/14629347/how-to-convert-unix-epoch-time-in-sqlite

Answers
Martin Zeitler 2019-01-14T02:03:50

1319017136629 is a value in milliseconds, which is not UNIX epoch time;\ntherefore it has to be divided by 1000 and rounded to integer seconds; only then DATE() and DATETIME() will convert.\n\nSELECT DATETIME(ROUND(dateColumn / 1000), 'unixepoch') AS isodate FROM tableName\n\n\nConverting database values to localtime is unfortunate; conversion on output is less problematic.\n\nThe Y2038 problem might already be worth a consideration.",

CL. 2013-01-31T16:21:29

To change the values in the database, use the UPDATE command:\n\nUPDATE MyTable SET MyColumn = datetime(MyColumn, 'unixepoch', 'localtime')\n",

Products recommended by Amazon
More >>>
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
 HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
 AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
 Timberland Men's 6-Inch Premium Waterproof Boot
Timberland Men's 6-Inch Premium Waterproof Boot
More about “How to convert Unix epoch time in SQLite” related questions
How to convert Unix epoch time in SQLite

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

Show Detail
How to convert Unix epoch time in SQLite

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

Show Detail
How to convert Unix epoch time in SQLite

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

Show Detail
convert unix epoch time to human readable time

First I have an Unix epoch time 1270787111. Then based on this website: http://untangible.com/2009/01/covert-unix-epoch-dates-in-microsoft-excel-including-timezone-examples.html, I convert the nu...

Show Detail
Convert Unix epoch time

I am trying to convert Unix epoch time to an ISO 8601 (https://en.wikipedia.org/wiki/ISO_8601) with the time zone. I have it working but only if it's local time. How can I pass in the time zone as...

Show Detail
Using unix() to create Unix epoch time

I want to create jalali moment and convert it to unix epoch time , Here is my code : const jalaliMoment = require('jalali-moment') let jalaliMoment1=jalaliMoment() app.post('/users', async ...

Show Detail
How to convert epoch unix time to TOD clock (STCK)?

Please can you help me to convert epoch Unix time to TOD clock (STCK) 8 bytes. e.g : EPOCH time in Hex = '175BC71AFE2'X EPOCH time in Decimal = 1,605,184,368,610 and I want to convert this decim...

Show Detail
Convert UNIX epoch time to SYSTEMTIME

In my C++ windows app I am converting a SYSTEMTIME to a formatted string like so: SYSTEMTIME systemTime; GetSystemTime(&systemTime); char pSystemTime[50]; sprintf_s(pSystemTime...

Show Detail
How to convert a date time string to long (UNIX Epoch Time) in Java 8 (Scala)

I want the UNIX Epoch Time (Posix Time, Unix Time) of a string in some pattern, the string is in normal format (so UTC). Please using Java 8, not Joda or old Java. (For milliseconds please see Ho...

Show Detail
How to Convert twitter api timestamp to unix time in vba excel

I am trying to convert a twitter timestamp string to Unix time (seconds since epoch). I have been searching for a while but cant find anything that works. Anyone know how to convert "Mon Sep 24 03:...

Show Detail