SQLite Forum

Global 'local time' setting?

Global 'local time' setting?

(1) By anonymous on 2020-10-07 09:52:19 [link] [source]

Is there some database level setting to always use local time (i.e. define the local timezone) instead of UTC time when doing date('now') or datetime('now')?

I don't want to have to specify the timezone parameter for each date related command in the application.

(2.1) By Keith Medcalf (kmedcalf) on 2020-10-07 11:18:09 edited from 2.0 in reply to 1 [source]


All datetime values are UT1 Zulu time and are assumed to be in UT1 Zulu time and will be converted to UT1 Zulu time if a timezone offset is specified when they are loaded/read and will be returned sans offset (naive). The 'localtime' and 'utc' will apply the appropriate offset (well, what the local computer conceives is the correct offset) to convert between naively expressed (offsetless) 'utc' and 'localtime' respectively.

Note I specify UT1 because the time is UT1 time, not UTC. UTC technically permits there to be a variable number of seconds in a minute but the datetime functions require that there be exactly 60 seconds in a minute so it is really UT1 time, not UTC.

See https://mperdikeas.github.io/utc-vs-ut1-time.html for an interesting explanation.

(3) By Stephan (stephancb) on 2020-10-07 20:45:40 in reply to 2.1 [link] [source]

UT1 is also off from UTC by up to 900 milliseconds. If for example a timestamp is obtained as SELECT datetime('now'); then this is UTC (assuming the clock of the computer, or smartphone is synchronized with a standard configured NTP client). To convert it to UT1 one can

  1. download from https://www.iers.org/IERS/EN/Science/EarthRotation/UT1LOD.html the Earth rotation data which include the preliminary and final DUT1 (UT1-UTC) and correct the UTC timestamp for the offset.

  2. in the US register at the NIST, https://www.nist.gov/pml/time-and-frequency-division/time-services/ut1-ntp-time-dissemination, and configure the NTP client such that it synchronizes instead to the NIST UT1 NTP server.

Both are quite tedious, I doubt that many Sqlite users are having datetime in "UT1 Zulu time".

The Sqlite documentation is correct:

"Universal Coordinated Time (UTC) is used."

"It is also assumed that every day is exactly 86400 seconds in duration."

This is together is not the same as using UT1