Retrieving the local timezone inside SQLite

I have an SQLite table containing a "last changed" column with a date in the format e.g. "2022-11-07T11:51:06+01:00". Coreutils' date outputs this by using the following command:

date +%FT%T%:z

I can almost generate it from inside SQLite by using

SELECT STRFTIME('%Y-%m-%dT%H:%M:%S', DATETIME('now', 'localtime'));

however, this lacks the timezone, and as far as I grasp the docs, there's no timezone placeholder.

So: Can I get the current local timezone using SQLite functions?

You cannot extract the timezone, but you can calculate it.

ROUND((JULIANDAY('now', 'localtime') - JULIANDAY('now')) * 24)   -- this gives the number of hours (signed)
ABS(ROUND((JULIANDAY('now', 'localtime') - JULIANDAY('now')) * 24 * 60) % 60)    -- this gives the number of minutes (unsigned)

The formulas above will give the difference in hours and minutes, of localtime from UTC. You can concatenate this info in your last changed column to add the timezone info.

SELECT STRFTIME('%Y-%m-%dT%H:%M:%S', DATETIME('now', 'localtime')) || PRINTF('%+.2d:%.2d', ROUND((JULIANDAY('now', 'localtime') - JULIANDAY('now')) * 24), ABS(ROUND((JULIANDAY('now', 'localtime') - JULIANDAY('now')) * 24 * 60) % 60));

StackExchange Database Administrators Q#319289, answer score: 4