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

Question

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?

Solution 1

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?

Solution 2

simply use local time as the default:

CREATE TABLE whatever(
     ....
     timestamp DATE DEFAULT (datetime('now','localtime')),
     ...
);

Solution 3

You should, as a rule, leave timestamps in the database in GMT, and only convert them to/from local time on input/output, when you can convert them to the user's (not server's) local timestamp.

It would be nice if you could do the following:

SELECT DATETIME(col, 'PDT')

...to output the timestamp for a user on Pacific Daylight Time. Unfortunately, that doesn't work. According to this SQLite tutorial, however (scroll down to "Other Date and Time Commands"), you can ask for the time, and then apply an offset (in hours) at the same time. So, if you do know the user's timezone offset, you're good.

Doesn't deal with daylight saving rules, though...

Solution 4

In the (admitted rare) case that a local datatime is wanted (I, for example, store local time in one of my database since all I care is what time in the day is was and I don't keep track of where I was in term of time zones...), you can define the column as

"timestamp" TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M','now', 'localtime'))

The %Y-%m-%dT%H:%M part is of course optional; it is just how I like my time to be stored. [Also, if my impression is correct, there is no "DATETIME" datatype in sqlite, so it does not really matter whether TEXT or DATETIME is used as data type in column declaration.]

Solution 5

SELECT datetime(CURRENT_TIMESTAMP, 'localtime')

View more solutions

Comments

[csl]

Storing timestamps in UTC is considered best practice. Convert to local time when presenting them.

[Ciro Santilli OurBigBook.com]

POSIX defines timestamps as UTC: stackoverflow.com/a/34107910/895245

[NoChance]

Not sure if this kind of trouble faces other types of databases. You'd imagine that if you create a database in Japan, you could use the data in UK!

[Vadim Peretokin]

This does have the problem of not being transferable across timezones however, no?

[Rohutech]

yes it is, its not working for me either, I am using sqlite in ZF2

[MKesper]

@xFighter I know my application is never going to need other way... Until you forget about that or someone else is using it.

[PodTech.io]

for unxitime; SELECT strftime('%s','now','localtime');

[NoChance]

sqlite localtime may be less important in time stamps and in applications where the end user does not use the data directly via a BI tool. However, localtime is what you need to use to store all application specific dates such as birthdate.

[NoChance]

Is this different from the answer provided by user hoju, CREATE TABLE whatever( .... timestamp DATE DEFAULT (datetime('now','localtime')), . );?

[Falco]

Please be careful with this - SQLite3 will default to storing DateTime Values as a simple integer Unix-Timestamp, without additional timezone-information. If coupled with localtime, sqlite will simply add/substract from this number and save effectively a wrong timestamp (because it will not represent the number of seconds since the start of unix-time) - and connecting with a client with other locale settings will display wrong times, without an easy way to correct.

[Falco]

If you use DATETIME Sqlite will store the value as an integer internally, which could have a big impact on query performance, row-size and caching if a datetime value is a 64bit integer vs. a 20byte string