UTC time wrong in SQLite

Question

My Centos 7.6 server is physically located in Germany but is configured so that it presents me (in London) with the correct local time and UTC time.

$ date
Tue Jul 16 08:31:51 BST 2019
$ date -u
Tue Jul 16 07:31:55 UTC 2019

But inside an SQLite database, things aren't right.

$ !sql
sqlite3 apollo.db 
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select datetime('now');
2019-07-16 07:32:48
sqlite> select datetime('now', 'localtime');
2019-07-16 08:32:53
sqlite> select datetime('now', 'utc');
2019-07-16 06:32:58

It's displaying the local time correctly, but UTC is an hour out. Is there a config setting I can tweak to fix this?

Update: Ok. Having read the documentation a bit more carefully, it seems I was misunderstanding and this behaviour is a) correct and b) expected. select datetime('now') is the correct way to get UTC time. So now I'm just a bit confused as to what select datetime('now', 'utc') is doing (probably nothing useful).

The "utc" modifier is the opposite of "localtime". "utc" assumes that the string to its left is in the local timezone and adjusts that string to be in UTC. If the prior string is not in localtime, then the result of "utc" is undefined. [SQL As Understood By SQLite - Date And Time Functions](https://www.sqlite.org/lang_datefunc.html) — MikeT, Jul 16 '19 at 10:43

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

Update: Ok. Having read the documentation a bit more carefully, it seems I was misunderstanding and this behaviour is a) correct and b) expected. select datetime('now') is the correct way to get UTC time. So now I'm just a bit confused as to what select datetime('now', 'utc') is doing (probably nothing useful).

The last paragraph before the examples explains by saying :-

The "utc" modifier is the opposite of "localtime".

"utc" assumes that the string to its left is in the local timezone and adjusts that string to be in UTC.

If the prior string is not in localtime, then the result of "utc" is undefined.

SQL As Understood By SQLite - Date And Time Functions

UTC time wrong in SQLite

1 Answers1