On Sun, 13 Dec 2015 20:11:32 -0700
Scott Robison <scott at casaderobison.com> wrote:
> > It's not fixed, although gacial progress is being made. Even though
> > we've had the TZ database & Posix datetime functions since 1986, 30
> > years later we're still struggling with it, and not only on Windows.
>
> The problem would be that SQLite could not depend on the presence of
> TZ functions even if they were added to the standard:
I think the time when "the standard" mattered regarding *libraries* has
passed. Nowadays, new functions do or don't get added to libc largely
based on what GNU does, and to a lesser extent on the BSD projects.
> 1. SQLite generally avoids non ANSI C so as to be compatible with the
> largest possible number of platforms. ANSI C (aka C89 or C90 for the
> ISO version) will never be updated to add new requirements.
SQLite maintains its own implementation of not a few functions for the
sake of compatibility. I don't know whether this should be one of
them, but there is more than enough precedent.
> 2. Let's say that that the next version of the C standard does add TZ
> functionality.
I haven't peeked to find out how SQLite implements date arithmetic. I
assume it parses strings in the database, calls mktime(3), and
subtracts time_t values. That's pretty vanilla, and doesn't *require*
the TZ database.
The downside of using mktime is that it locks you into a "time zone
perspective", if you will. The timezone that will be used to convert
a (UTC-based) time_t value to "local time" is set globally. If you
want to compare two local times, you have to manipulate that global
variable between conversions.
The new mktime_z(3) function from NetBSD unglobalizes the timezone: it
adds a timezone parameter. That makes it much more convenient to use
(if that's what you need!) It's been accepted afaict by IANA, but I
found no discussion of it at GNU.
While the NetBSD (and IANA, obviously) implementation uses the TZ
database, that's not a requirement. The function's definition makes no
reference to its implementation.
mktime_z could be emulated on Windows without IANA's help. Which it
would have to be, because Windows doesn't use the TZ database:
save TZ
set TZ to something
_tzset() // Microsoft!
mktime
restore TZ
_tzset
A quick glance at the documentation suggests
TzSpecificLocalTimeToSystemTimeEx might be useful, too.
Someone will complain that would be slow, and something about
threads. My understanding is that the OP got the wrong answer, and I
would say slow is better than broken. And it won't be slow: there's no
I/O; not even a context switch.
As Keith said, as of now you have to roll your own. SQLite does not
support date arithmetic across time zones. Should it? Should it as an
extension? I don't know. I was just trying to understand (and
explain) what the C foundation looks like, why/how it's broken, and
what would be required to fix it.
--jkl
