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

Question

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?

simply use local time as the default:

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

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

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

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.