The "'utc'" term is an operator that says: "Assume that the time to the left is localtime and convert it into UTC."

In your case, converting localtime to UTC means subtracting two hours. And that is exactly what the 'utc' operator did. In your example, you started with UTC, however, and then subtracted an additional two hours, which is not what you wanted, apparently.

There is some goofiness in the design of 'utc' in that if the date to the left is explicitly already in UTC (for example: '2022-03-23 11:22:29Z' with the "Z" at the end) then the 'utc' is a no-op. I now understand that that design was a bad idea. But it has been that way for nearly 20 years, and millions of applications have been written against that design, so I dare not change it now for fear of breaking legacy.

I get the sentiment of it being a bad design that now we are stuck with, but I'm not buying the premise.

What change would make it better? - Say you had a clean slate to start from scratch today. Would you make the date-time (par 1) ALWAYS be UTC? Would you remove the "UTC" modifier? I can think of things that become hard to do in both those changes.

Maybe there's an easy change I'm missing, but I feel like the current method has such simple rules that makes it most elegant and beautiful.

VALUE, modifier, modifier, [modifier, ...]. Beautiful.

(i.e. my hope is it stays the same in SQLite4)

The change would be:

• 'localtime' always shifts UTC→localtime.

• 'utc' always shifts localtime→UTC.

The 'localtime' modifier currently behaves as described, but 'utc' does not. The 'utc' modifier only works if:

• The time to the left did not have an explicit timezone mark. In other words, the 'utc' modifier is a no-op if the time to the left is '2022-03-23 12:26:47Z' (with the "Z") or '2022-03-23 08:26:47-04:00' (with the "-04:00" timezone specifier).

• The time to the left has not been previously modified using a prior 'utc'.

A proper fix, it seems to me, would be to omit these exceptions and make 'utc' always shift the time, just as 'localtime' does.

Not enough precision in floating-point numbers for that. Maybe you should say:

... timestamp TEXT not null default (strftime('%Y%m%d%H%M%f','now')) ...

I have a modification to date.c which fixes the datetime handling but is not fully backwards compatible which maintains the DateTime structure so that it is always an "instant time" or rather a "localized time" as opposed to a "naive time" -- the iJD is maintained (wherever possible) as a proper UT1 offset, and maintains a separate "tzoffset" (rotational angle) of the "display string". All input permits an proper offset, and the 'utc' and 'localtime' operators only change the offset once the iJD has been properly set relative to UT1 (though an operator has been added 'naive' to turn a "instant time" into a "naive time" (iJD is relative to some arbitrary and unknown Meridian).

It is a "drop in" replacement for the existing date.c code, but is not fully backward compatible (several of the test cases will fail). However, you cannot "mess up" the datetime value with nonsensical operations. (It does not have a bug-for-bug compatibility and that is not fully restored even by adding a 'naive' operator after every modifier is processsed.

It also adds a DATETIME collation which provides "total ordering" for a column which ostensibly contains properly formatted ISO datetime strings. (which provides a total ordering different than merely ORDER BY julianday(...))

The code can be found here: http://www.dessus.com/files/date.c
and the collection including a text description of the changed behaviour can be found in http://www.dessus.com/files/sqlite3extensions.zip

I used to monkeypatch the datetime functions to work correctly. This works better. It is, however, not entirely bug-for-bug compatible.

According to wikipedia, SQL:2016 added "Date and time formatting and parsing". Does anybody have more detailed information about that? I tried googling but couldn't find anything useful. Maybe SQL:2016 has added some concepts which could help solving the UTC/localtime inconsistency without breaking existing applications.

From a bit of searching, all I could find was this tiny nibble of information:

T839 Formatted cast of datetimes to/from character strings

… at the bottom of a blog post entitled What’s New in SQL:2016.

It is unlikely to contain any revolutionary new insight, I think. Local time and timezones in general is just inherently messy, and there is no magic bullet that will solve all related problems. In effect, every application programmer has to deal with it using the tools already available.

Anything in the second, third, etc. parameters to the datetime function (and its ilk) are "modifiers" in that they modify the date-time to the left, in order from left to right.

so if you give it any random date-time and then add no modifiers, it uses that as-is and assumes it to be in UTC.

Adding a modifier to the right of 'localtime' will see it change that time (assumed to be utc) into the local timezone by adding the offset.

Adding a modifier of 'utc' will assume the date-time has to be converted to utc and so it subtracts the offset.

Adding a modifier of 'localtime' and then one of '+7 days' will first add the offset and then add 7 days to the date-time. etc. etc.

I agree it's not very obvious at the onset, but once you take a step back and look at it in overview, it quickly becomes quite clear.