CODEWITHSUNDEEP
sqlsqlitetimezonetimestamp
BrianH
BrianH

Reputation: 8241

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

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?

Upvotes: 166

Views: 269419

Answers (11)

BrianH
BrianH

Reputation: 8241

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?

Upvotes: 193

Jens A. Koch
Jens A. Koch

Reputation: 41786

SELECT datetime(CURRENT_TIMESTAMP, 'localtime')

In short: the CURRENT_TIMESTAMP is UTC and converted to localtime.

The "localtime" modifier (21) assumes the time value to its left is in Universal Coordinated Time (UTC) and adjusts that time value so that it is in localtime. If "localtime" follows a time that is not UTC, then the behavior is undefined. The "utc" modifier is the opposite of "localtime". "utc" assumes that the time value to its left is in the local timezone and adjusts that time value to be in UTC. If the time to the left is not in localtime, then the result of "utc" is undefined. -- Source: https://www.sqlite.org/lang_datefunc.html#modifiers

Upvotes: 21

Melroy van den Berg
Melroy van den Berg

Reputation: 3215

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

Upvotes: 4

Julian
Julian

Reputation: 71

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

Upvotes: 7

liquide
liquide

Reputation: 1383

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

Upvotes: 12

Joseph
Joseph

Reputation: 207

The current time, in your machine's timezone:

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

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

Upvotes: 2

Kenneth Navarro
Kenneth Navarro

Reputation:

I think this might help. 

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

Upvotes: 2

Patrick
Patrick

Reputation:

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.

Upvotes: 13

hoju
hoju

Reputation: 29472

simply use local time as the default:

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

Upvotes: 104

polyglot
polyglot

Reputation: 10165

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

Upvotes: 20

Roger Lipscombe
Roger Lipscombe

Reputation: 91925

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

Upvotes: 25

Related Questions