After upgrading from 3.37.2 to 3.38.0, strftime's %f (fractional seconds) substitution no longer works correctly.

Here's a simpler repo which doesn't need the table:

$ fossil sql # current sqlite3 prerelease version
SQLite version 3.39.0 2022-03-02 01:02:16
...
sqlite> SELECT strftime('%Y-%m-%d %H:%M:%f', 1.234, 'unixepoch', 'localtime');
'1970-01-01 01:00:01.000'


$ sqlite3
SQLite version 3.37.2 2022-01-06 13:25:41
...
sqlite> SELECT strftime('%Y-%m-%d %H:%M:%f', 1.234, 'unixepoch', 'localtime');
1970-01-01 01:00:01.234

strftime and printf haven't changed. The problem is more likely to be with the 'unixepoch' modifier.

Yes. The new calculations are broken. (function isDate in date.c)

Instead of finding the "offset" and applying it, the code computes the new time using functions that are only accurate to the integer second.

This is secondary to the inherent defect that the iJD is not relative to UTC but is rather relative to some arbitrary and unidentified meridian. You can see this easily with julianday('now') and julianday('now', 'localtime'), both of which should return the same offset from the julian epoch in days. Similarly the unix epoch is relative to an arbitrary (and unidentified) meridian, rather than using a unix epoch that is relative to UTC.

SQLite version 3.38.0 2022-02-22 18:58:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select julianday('now'), julianday('now','localtime');
┌──────────────────┬──────────────────────────────┐
│ julianday('now') │ julianday('now','localtime') │
├──────────────────┼──────────────────────────────┤
│ 2459642.68405848 │ 2459642.39238426             │
└──────────────────┴──────────────────────────────┘
sqlite> select unixepoch('now'), unixepoch('now','localtime');
┌──────────────────┬──────────────────────────────┐
│ unixepoch('now') │ unixepoch('now','localtime') │
├──────────────────┼──────────────────────────────┤
│ 1646367933       │ 1646342733                   │
└──────────────────┴──────────────────────────────┘
sqlite>

Note that the isDate localtime and utc modifiers can be fixed to compute and apply the transformation as an offset rather trivially -- this will still not convert the datetime functions to use UTC based epochs, the epoch will remain relative to an arbitrary meridian rather than the Prime Meridian.

The datetime functions are lilian (as in not relative to UTC), what are commonly known as naive datetime's -- they do not identify a specific point in time.

Fixing the datetime functions to be relative to UTC is likely to be a breaking change, however, because fiddle-arounds to handle naive datetime's is rather common (and often done wrongly).

Thanks for your care in reporting this. The fix will appear in a soon-to-be-released, minor revision of the SQLite library.