3

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?

Tobias Leupold
  • 137
  • 1
  • 7

2 Answers2

4

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));
Andrea B.
  • 1,731
  • 8
  • 13
1

You can get the difference in seconds between UTC and your local like this:

SELECT unixepoch('now', 'localtime') - unixepoch('now') as `timezone difference in seconds`

Note that unixepoch() is available from SQLite version 3.38.0 and the above line is equivalent to

SELECT cast(strftime('%s', 'now', 'localtime') as int) - cast(strftime('%s', 'now') as int) as `timezone difference in seconds`

From there, you can get difference in hours:

SELECT (unixepoch('now', 'localtime') - unixepoch('now')) / 3600 as `hours difference`

...and in minutes:

SELECT (unixepoch('now', 'localtime') - unixepoch('now')) % 3600 / 60 as `minutes difference`

Put this into the final string (local time formatted [+-] abs(hours difference) : abs(minutes difference)):

SELECT printf(
    "%s%c%.2d:%.2d",
    strftime("%Y-%m-%dT%H:%M:%S", 'now', 'localtime'),
    case when (unixepoch('now', 'localtime') - unixepoch('now') < 0) then '-' else '+' end,
    abs((unixepoch('now', 'localtime') - unixepoch('now')) / 3600),
    abs((unixepoch('now', 'localtime') - unixepoch('now')) % 3600 / 60)
) as `local time with timezone`

For example, suppose your timezone is exactly +02:40 before UTC, and UTC is 2024-08-01 09:20:00:

SELECT
    datetime    ('2024-08-01 09:20:00') as `utc time`,
    datetime    ('2024-08-01 09:20:00', '+2 hours', '+40 minutes') as `local time`,
    unixepoch   ('2024-08-01 09:20:00', '+2 hours', '+40 minutes') - unixepoch('2024-08-01 09:20:00') as `timezone difference in seconds`,
    printf(
        "%s%c%.2d:%.2d",
        strftime("%Y-%m-%dT%H:%M:%S",
                               '2024-08-01 09:20:00', '+2 hours', '+40 minutes'),
        case when   (unixepoch('2024-08-01 09:20:00', '+2 hours', '+40 minutes') - unixepoch('2024-08-01 09:20:00') < 0) then '-' else '+' end,
                abs((unixepoch('2024-08-01 09:20:00', '+2 hours', '+40 minutes') - unixepoch('2024-08-01 09:20:00')) / 3600),
                abs((unixepoch('2024-08-01 09:20:00', '+2 hours', '+40 minutes') - unixepoch('2024-08-01 09:20:00')) % 3600 / 60)
    ) as `local time with timezone`

This would produce

UTC time local time timezone diff local time with timezone
2024-08-01 09:20:00 2024-08-01 12:00:00 9600 2024-08-01T12:00:00 +02:40

I don't like how i have to repeat ('now', 'localtime') etc. everywhere, but i couldn't come up with anything better ¯\(ツ)/¯

Soreg
  • 11
  • 2