Alex Rivera | Logout

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

Asked 2008-12-19T15:53:32.940
167

I have a 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?

sql sqlite timezone timestamp
Edit
Report

2 Answers

195

I found on the sqlite documentation (https://www.sqlite.org/lang_datefunc.html) this text:

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

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

That didn't look like it fit my needs, so I tried changing the "datetime" function around a bit, and wound up with this:

SELECT datetime(timestamp, 'localtime')

That seems to work - is that the correct way to convert for your timezone, or is there a better way to do this?

answered 2008-12-19T16:04:28.907
2

The current time, in your machine's timezone:

select time(time(), 'localtime');

As per http://www.sqlite.org/lang_datefunc.html

answered 2012-11-14T02:22:32.480

Your Answer