Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine

Question

I have an SQLite (v3) table with this column definition:

"timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP

The server that this database lives on is in the CST time zone. When I insert into my table without including the timestamp column, SQLite automatically populates that field with the current timestamp in GMT, not CST.

Is there a way to modify my insert statement to force the stored timestamp to be in CST? On the other hand, it is probably better to store it in GMT (in case the database gets moved to a different timezone, for example), so is there a way I can modify my select SQL to convert the stored timestamp to CST when I extract it from the table?

Answer 1

To compute the date and time given a unix timestamp 1092941466, and to compensate your local timezone try this:

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

You can try changing the "datetime" function around a bit like this:

select datetime(timestamp, 'localtime')

For more information, you can refer to SQLite documentation (https://www.sqlite.org/lang_datefunc.html)