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?

*Storing* timestamps in UTC is considered best practice. Convert to local time when *presenting* them. — csl, Oct 12 '15 at 21:56
POSIX defines timestamps as UTC: http://stackoverflow.com/a/34107910/895245 — Ciro Santilli OurBigBook.com, Dec 05 '15 at 18:53
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! — NoChance, Oct 11 '19 at 02:23

score 183 · Accepted Answer · edited Oct 17 '15 at 03:04

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?

score 97 · Answer 2 · answered May 30 '10 at 14:42

97

simply use local time as the default:

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

answered May 30 '10 at 14:42

hoju

28,392
37
134
178

9

This does have the problem of not being transferable across timezones however, no? – Vadim Peretokin Sep 09 '13 at 07:40
yes it is, its not working for me either, I am using sqlite in ZF2 – Rohutech May 13 '15 at 15:19
2

@xFighter _I know my application is never going to need other way_... Until you forget about that or someone else is using it. – MKesper May 29 '17 at 07:44
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 Aug 27 '20 at 09:53

Roger Lipscombe · Answer 3 · 2008-12-20T08:11:01.937

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

score 20 · Answer 4 · answered Feb 21 '09 at 03:38

20

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

answered Feb 21 '09 at 03:38

polyglot

9,945
10
49
63

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 Oct 11 '19 at 02:05
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 – Falco Aug 27 '20 at 09:59

score 13 · Answer 5 · edited May 30 '10 at 15:07

When having a column defined with "NOT NULL DEFAULT CURRENT_TIMESTAMP," inserted records will always get set with UTC/GMT time.

Here's what I did to avoid having to include the time in my INSERT/UPDATE statements:

--Create a table having a CURRENT_TIMESTAMP:
CREATE TABLE FOOBAR (
    RECORD_NO INTEGER NOT NULL,
    TO_STORE INTEGER,
    UPC CHAR(30),
    QTY DECIMAL(15,4),
    EID CHAR(16),
    RECORD_TIME NOT NULL DEFAULT CURRENT_TIMESTAMP)

--Create before update and after insert triggers:
CREATE TRIGGER UPDATE_FOOBAR BEFORE UPDATE ON FOOBAR
    BEGIN
       UPDATE FOOBAR SET record_time = datetime('now', 'localtime')
       WHERE rowid = new.rowid;
    END

CREATE TRIGGER INSERT_FOOBAR AFTER INSERT ON FOOBAR
    BEGIN
       UPDATE FOOBAR SET record_time = datetime('now', 'localtime')
       WHERE rowid = new.rowid;
    END

Test to see if it works...

--INSERT a couple records into the table:
INSERT INTO foobar (RECORD_NO, TO_STORE, UPC, PRICE, EID)
    VALUES (0, 1, 'xyz1', 31, '777')

INSERT INTO foobar (RECORD_NO, TO_STORE, UPC, PRICE, EID)
    VALUES (1, 1, 'xyz2', 32, '777')

--UPDATE one of the records:
UPDATE foobar SET price = 29 WHERE upc = 'xyz2'

--Check the results:
SELECT * FROM foobar

Hope that helps.

Is this different from the answer provided by user hoju, CREATE TABLE whatever( .... timestamp DATE DEFAULT (datetime('now','localtime')), . );? — NoChance, Oct 11 '19 at 02:13
Its not the cleanest of approaches but it works. it correctly adjusts the time and since I use the sqlite only for a smaller mutation queue its ok to violate it that way. — Jan S., Jun 13 '23 at 19:27

score 12 · Answer 6 · answered Apr 22 '15 at 14:36

12

SELECT datetime(CURRENT_TIMESTAMP, 'localtime')

answered Apr 22 '15 at 14:36

Jens A. Koch

39,862
13
113
141

score 9 · Answer 7 · answered Jan 16 '14 at 17:53

9

SELECT datetime('now', 'localtime');

answered Jan 16 '14 at 17:53

liquide

1,346
3
20
28

score 6 · Answer 8 · edited Nov 11 '15 at 12:16

6

Time ( 'now', 'localtime' ) and Date ( 'now', 'localtime' ) works.

edited Nov 11 '15 at 12:16

Matt

74,352
26
153
180

answered Nov 07 '14 at 05:26

Julian

61
1
1

score 4 · Answer 9 · answered Feb 03 '16 at 18:01

You can also just convert the time column to a timestamp by using strftime():

SELECT strftime('%s', timestamp) as timestamp FROM ... ;

Gives you:

1454521888

'timestamp' table column can be a text field even, using the current_timestamp as DEFAULT.

Without strftime:

SELECT timestamp FROM ... ;

Gives you:

2016-02-03 17:51:28

score 2 · Answer 10 · answered Nov 14 '12 at 02:22

2

The current time, in your machine's timezone:

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

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

answered Nov 14 '12 at 02:22

Joseph

207
3
10

score 2 · Answer 11 · edited May 19 '11 at 12:53

2

I think this might help.

SELECT datetime(strftime('%s','now'), 'unixepoch', 'localtime');

edited May 19 '11 at 12:53

Oleks

31,955
11
77
132

answered Feb 20 '09 at 23:46

for unxitime; SELECT strftime('%s','now','localtime'); – PodTech.io Oct 17 '17 at 11:20

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

11 Answers11

Linked

Related