Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
3 views
in SQL by (20.3k points)

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

1 Answer

0 votes
by (40.7k points)

To compute the date and time given a unix timestamp 1092941466, and to compensate your local timezone try this:

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

You can try changing the "datetime" function around a bit like this:

select datetime(timestamp, 'localtime')

For more information, you can refer to SQLite documentation (https://www.sqlite.org/lang_datefunc.html)

Related questions

0 votes
1 answer
asked Jul 26, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
asked Oct 7, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...