SQLite Forum

strftime bug (3.38.0)

strftime bug (3.38.0)

(1) By buster on 2022-03-04 03:22:35 [source]

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

# create a database and insert a real
sqlite3 -batch sqlite3-test.db "CREATE TABLE Test (x REAL)"
sqlite3 -batch sqlite3-test.db "INSERT INTO Test VALUES(1.234)"
sqlite3 -batch sqlite3-test.db "SELECT strftime('Expected: 1970-01-01 00:00:01.234, Actual: %Y-%m-%d %H:%M:%f', x, 'unixepoch', 'localtime') FROM Test"

Output on version 3.37.2:

Expected: 1970-01-01 00:00:01.234, Actual: 1970-01-01 00:00:01.234

Output on version 3.38.0:

Expected: 1970-01-01 00:00:01.234, Actual: 1970-01-01 00:00:01.000

(2) By Stephan Beal (stephan) on 2022-03-04 03:33:52 in reply to 1 [link] [source]

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

(3.2) By buster on 2022-03-04 03:44:18 edited from 3.1 in reply to 2 [link] [source]

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

(4.3) By Keith Medcalf (kmedcalf) on 2022-03-04 04:48:23 edited from 4.2 in reply to 3.2 [link] [source]

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                   │

(5) By Keith Medcalf (kmedcalf) on 2022-03-04 04:36:50 in reply to 4.2 [link] [source]

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).

(6) By Larry Brasfield (larrybr) on 2022-03-05 20:56:05 in reply to 1 [link] [source]

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