Since sqlite3 does not expose a way to return the local time zone UTC offset, I wrote this heuristic query. This usually works in any time zone (see limitations). If anyone knows a better way to do this without modifying the SQLite code and in pure SQLite please share it!

SELECT DATETIME('now') || PRINTF(' %+05d', STRFTIME('%H%M', DATE('now')||'T12:00', 'localtime') - STRFTIME('%H%M', DATE('now')||'T12:00'));

Note: DATE('now') needs to be replaced with the DATE() of the timestamp of interest.

Explanation:

PRINTF('%+05d', x) modifies:

• + => Sign the integer with + or -

• 05d => Prepend the number with 0 to pad it to 5 characters

STRFTIME('%H%M', DATE('now')||'T12:00', <'localtime' or null>) modifies:

• %H%M => Output only hour and minute

• DATE('now') => Placeholder for date of timestamp. Necessary to deal with Daylight Savings Time correctly.

• || => Concatenate

• T12:00 => Compare times at noon to prevent inverse time zone difference due to the hour wrapping around at 24

• 'localtime' (former only) => SQLite internally converts the time to local time from UTC if this option is passed.

Limitations:

• Only gives equivalent time zones in countries next to the International Date Line with +/- 12, +13, and +14 codes (+0, +1, +2)

• Since the Daylight Savings Time is inferred from the date (which changes at 12am), but DST does not change at midnight (e.g. 2am in Montreal, Canada), the time zone will be incorrect for timestamps before the DST time change.

  • DST STARTS: In Montreal on 2022-03-13, T02:00-5000 becomes T03:00-4000

    • A timestamp taken at T00:00-5000 (before the change) will be reported as T00:00-4000 (which does not exist)
    • A timestamp taken at T01:00-5000 (before the change) will be reported as T01:00-4000 (which does not exist)

  • DST ENDS: In Montreal on 2022-11-06, T02:00-4000 becomes T01:00-5000

    • A timestamp taken at T00:00-4000 (before the change) will be reported as T00:00-5000 (which does not exist)
    • A timestamp taken at T01:00-4000 (before the change) will be reported as T01:00-5000 (which is actually an hour later)

I find it strange that SQLite does not implement C's strftime('%z') for localtime output, but this is a partial built-in workaround.

Don't.... just DO NOT use local time for timestamps! If you want to see timestamps in local time leave that to a presentation layer.

You already showed several examples of invalid timestamps - UTC timestamps are always valid (as long as your clocks are synchronized, but that is another story). And imagine what happens if there is a bug in the conversion to local time. Now if this is for a presentation layer you can just fix the bug. But if you created timestamps based on a faulty calculation then what? Are you going to modify every row with a miscalculated timestamp? In some databases that would not even be possible without temporary changing the schema.

And even if the conversion is correct: if your local time goes from 03:00 from 02:00 you will have an hour of not only duplicate timestamps but even worse timestamps that are out of sequence!

Years ago I was employed at a bank where it was decided to use localtime for timestamps before the introduction of DST. The result? Every year after introduction of DST the mainframes had to be shut down for more than an hour during the switch from CEST to CET to avoid duplicate timestamps in the databases.

OP here. I agree, and it bears repeating your point for anyone reading - always store timestamps in UTC in the database.

It is however useful from time to time to output a VIEW with a timestamp in localtime for presentation purposes. In such cases, appending a time zone offset like this will decrease (but not eliminate) such collisions as you described.

I'm sure there's a craftier query possible that would solve for those two remaining ambiguous hours each year. Curious to see if anyone else has solved it.

I just load the IANA Timezone database, and use that. The root of the problem is failure to understand what is a "Point in Time". Once someone figures out what is a "point in time" all problems become non-existant.

That is to say that you cannot store "utc" in the database (at least ot in any format that can be used by the builtin datetime functions).

I suspect what you mean (but are not communicating properly) is to always store an unambiguous UT1 Point-in-Time value.

Anonymous from (2)

Before digging into the IANA Timezone database and starting coding you might want to view The Problem with Time & Timezones - Computerphile.

And indeed, store as UTC and delegate the presentation in local time to a view.

Sqlite will happily convert between UTC and local system time with all of its time and date functions.

If you want something more specific or esoteric then yes, you will need to do that in a different layer of your application.

I'm not sure that this is anything other than extremely prudent for a "lite" database library.

Timezones and datetimes drive people bonkers.

You already showed several examples of invalid timestamps - UTC timestamps are always valid

SQLite3 does not accept UTC datetime's, only UT1 datetime's (POSIX time) (that means that a day always contains exactly 24 hours, each hour always contains exactly 60 minutes, each minute always contains exactly 60 seconds.

UTC permits "leap seconds" (where an hour may contain 59 or 61 seconds).

UTC is not permitted in an SQLite3 datetime.

What you are calling a "UTC timestamp" is a misnomer. What you are describing is called UT1 Instant Time. It is a descriptor which identifies, unambiguously, exactly one single point-in-time (on the UT1 scale). It may contain an "offset" from the Prime Meridian or it may be at the Prime Meridian (offset 0, or GMT, or Zulu, or whatever you want to call it -- it is not by any strech of the imagination, UTC).

Each UT1 Instant in Time occurs exactly once. Ever. With no duplicates.

There are many ways to "display" a UT1 datetime. In fact, if we were to permit the "offset" to be measured in "seconds" and range from -86400 to +86400 (two whole days) then there would be 172800 different "spellings" of any single Instant-in-Time. All would be equally valid and all would identify the same instant in time.

A valid instant time contains a reference to a specific UT1 instant.

SQLite3 datetime functions do not maintain an "offset" and, although they may have an offset on input, that offset is not maintained and the UT1 value is assumed to be relative to the Prime Meridian. If the datetime value is relative to a local meridian, then there is no way to know what that meridian is, nor what UT1 instant time is represented. Also, there is no way to know a correct "julianday" or "unixepoch" number since those are, by definition, measures relative to the Prime Meridian based on UT1.

Sorry, this is also not correct, though it has nothing to with SQLite: The difference between UTC and UT1 is not only the leap second, they are also different by up to almost one second. You can check the difference at NIST, scroll down to "Current UT1-UTC values". Presently the difference is 40 milliseconds, decreasing, but it can theoretically become nearly a second and has in the past been several 100 milliseconds. The difference between UT1 and UTC is because the former is based on the Earth's rotation and the latter on atomic clocks.

The clocks in computers, smartphones, etc are nowadays synchronized via the NTP protocol, and NTP synchronizes to UTC, not UT1. Therefore, when you do

sqlite> SELECT datetime('now');
2022-10-26 15:45:04

the returned timestamp is UTC, not UT1 which would presently be about 40 milliseconds off, and SQLite is happy to accept this UTC time stamp. If you do this command during a leap second, none of the OS in use will return a leap second time stamp, but an OS dependent "workaround". But, yes, if some OS were to return a leap second time stamp, SQLite would not accept it. Nevertheless practically all computerized timestamps are UTC thanks to the ubiquitous NTP protocol (except leap seconds, the OS cannot handle them).

SQLITE does not know whether a timestamp is UTC or UT1, but it is exposed to almost exclusively UTC time stamps. "utc" is correctly used in the SQLite documentation.

Anonymous from (2)

Are we talking about the same SQLite? Because when I read Date And Time Functions I see many references to UTC but none to UT1.

So, either the documentation is wrong or..... Since SQLite actually does not accept '2016-12-23 23:59:60' (the most recent leapsecond) the former might be true.

And just being curious: where would one obtain UT1 time? Browsing through the timezones in Windows all are relative to UTC. And in RFC5905 there are no references to UT1. Of course you could write your own NTP client and let it apply DUT1 - but really?

For timekeeping on a computer a time which is defined by a network of precision atomic clocks seems more appropriate than one which is defined by Earth's rotation.

UTC is an artifice (does not actually exist). It is an "average" of a bunch of measurements (none of which are accurate). The estimates are based on TAI seconds (arbitrarily defined number of "state oscillations" that occur in a specific atomic structure).

This "averaged pretty close but not the same" is periodically corrected to UT1 by inserting/deleting "leap seconds" thus "whupping along" the entire UTC timescale (if you learned Functions and Relations in High School then the "conversion" is not done by "adjusting the slope" and "applying an offset" as is done with other "mismatched scales" (eg, degC and degF) but rather by periodically "futzing" the "base" (jiggling the offset) withouit ever fixing/addressing the inherent error in the slope.

This is by design.

... based on TAI seconds (arbitrarily defined number of "state oscillations" that occur in a specific atomic structure).

Nit-picking: SI (not TAI) second, 9,192,631,770 oscillations of the radiation from some transition in the Caesium atom.

That SQLite date and time functions cannot represent time stamps in leap seconds is not a big problem and does not mean that it uses a different standard for time than almost everybody else in the world. I work with sensors which produce data strictly every second. Instead of throwing away the data in leap seconds, my tables have:

CREATE TABLE sw_efi_l0 (
       day2000 INTEGER,   -- days since 1 Jan 2000, 0 UT
       msec INTEGER,      -- milliseconds of day

       datestr TEXT GENERATED ALWAYS AS (date('2000-01-01', day2000||' days')) VIRTUAL,
       timestr TEXT GENERATED ALWAYS AS (time('2000-01-01', (msec/1000)||' seconds')) VIRTUAL,
       dttmstr TEXT GENERATED ALWAYS AS (strftime('%Y-%m-%d %H:%M:%f', '2000-01-01', day2000||' days', (msec/1000.0)||' seconds')) VIRTUAL,

       current_sensor1_1   INTEGER,
       ...
);

i.e. a day segmented timestamp which can represent leap second times. It does not use more space than a single INTEGER timestamp of the milliseconds since some epoch, because SQLite uses automatically only 1,2, or 4 byte integers. Querying certain days is quite easy. There is no need to maintain a table of past leap seconds, unless the time between time stamps is queried, which I never need.

That first DATETIME() should be in localtime, so the query becomes:

SELECT DATETIME('now', 'localtime') || PRINTF(' %+05d', STRFTIME('%H%M', DATE('now')||'T12:00', 'localtime') - STRFTIME('%H%M', DATE('now')||'T12:00'));

You can get a hint abot utc offset running this query

select timediff(datetime('now', 'localtime'), datetime('now')) as utc_offset;

((edited to correct a missing parenthesis.))

You can get a hint abot utc offset running this query

Just for completeness's sake: that's true now, but timediff() was added in August 2023, so wasn't available when the OP posted.

select timediff(datetime('now', 'localtime'), datetime('now')) as utc_offset;
                                        needs a ')' here  ---^