Hi,

I'm writing a little "recollection helper", in which I want to store timestamps that "mean something to the user", i.e. "had breakfast at 9 am Pacific/Auckland" rather than 21:00 UTC.

I'm storing the actual timestamps in UTC (current_timestamp), and have a column for the ±HH:MM offset and another column for the timezone name, e.g. "Pacific/Auckland".

e.g.

create table timestamps ( id, utc, tz_offset, tz_name );
insert into timestamps values ( 1, datetime(), ???, ??? ); -- <-- this is what I want to optimise

My questions:

1. would it be possible for strftime to include the offset implicitly used by the 'localtime' modifier? Selecting (strftime('%s','now','localtime')-strftime('%s','now'))/60 and then formatting the result manually seems quite clunky.

2. is there any sane way to get the local timezone name at all, which I assume is being used to determine the current offset? (I know people still like to use 'CET' etc, but OS's are getting good at using proper tz names in the background these days).

I don't expect a 100% reliable solution for this, but it would still be useful if e.g. %Z or %z would provide the ±HH:MM offset and <area>/<location> timezone name, if available.

Finally, I discovered by accident that select datetime(utc,'+09:30') works, which is perfect for use with the schema above, but using ±HH:MM as a modifier is not documented in the list of valid modifiers in section 3 of https://sqlite.org/lang_datefunc.html. ( datetime('now','+0930') doesn't work - but I'm fine with that. )

Thanks,

Yet another Steve

Assumption: SQLite is being used as the embedded backing store for a client-centric application. A web service should obviously get timezone info from e.g. the user's browser.

would it be possible for strftime to include the offset implicitly used by the 'localtime' modifier? Selecting (strftime('%s','now','localtime')-strftime('%s','now'))/60 and then formatting the result manually seems quite clunky.

Anything is possible. It would not be likely, however.

is there any sane way to get the local timezone name at all, which I assume is being used to determine the current offset? (I know people still like to use 'CET' etc, but OS's are getting good at using proper tz names in the background these days).

There are loads of Operating System (and other) methods for doing this.

The datetime primitives in SQLite are primitive. They are designed to be primitive. Timezone support is non-existant. "localtime" is merely an interpolated construct.

You can "replace" the entire dataetime subsystem with one that uses localized time everywhere and can "do" proper timezone manipulation. However that is functionality that is unrelated to storing and retrieving of information.

And datetime('now','+0930') of course does not work because you have misformatted the modifier. It is not, in fact, documented that you may use a timestring as a modifier. ie, [+|-]HH:MM[:SS[.FFFFFFFFFF]]

What have you done there Keith? Generated a markdown table by mistake?

It is not, in fact, documented that you may use a timestring as a modifier. ie, [+-]HH:MM[:SS[.FFFFFFFFFF]]

While that's true, the code to support it is in the wild:

        /* A modifier of the form (+|-)HH:MM:SS.FFF adds (or subtracts) the
        ** specified number of hours, minutes, seconds, and fractional seconds
        ** to the time.  The ".FFF" may be omitted.  The ":SS.FFF" may be
        ** omitted.
        */

Is this a documentation oversight, or a situation akin to "private API, may morph without warning - DO NOT DEPEND ON THIS", or something else entirely?

I suspect that the 3rd paragraph of section 2 of the doc was intended to document the clearly intentional feature. (IOW, neither an oversight or secret unreliable API)

is there any sane way to get the local timezone name at all, which I assume is being used to determine the current offset

SQLite does not know either the timezone name or the offset. When SQLite3 needs to "compute the offset" it asks the Operating System to do so. The capabilities of various Operating Systems differ in how they achieve this.

Thanks for the quick answer - and no, I'm neither disappointed nor surprised... except for the fact that you responded on good Friday!?!? Take a break guys! ;-)

And as I said, the fact that +HHMM doesn't work doesn't bother me in the least. However +HH:MM ("properly" formatted), does work - and I think it should be documented. I for one had long avoided the (utc, offset, timezone) solution because I read the documentation and didn't see any reasonable way of storing the offset for UTC and local time use. Being able to use the same, human readable, offset format for ISO8601 and as a modifier is just elegant - it deserves more praise ;-)

Cheers and have a good long weekend!

SQLite does not know either the time zone name or the offset.

I do not think there is anything that is fully aware; the complications are:

1. Given local time which may have day light saving hours, there is its UTC equivalent which has an offset which will vary depending on day light saving.

2. Time zones can at a stretch be derived programmatically using longitude but then you will need the polygons of every country.

3. Time zones do not follow a rigorous formula: some countries that should have multiple time zones because of their location retain a single administrative time zone for political reasons.

For my application (shipping, where there may be restrictions applying on time of day) I ended up with a table of the relevant time metrics.