[sqlite] Bug with DATETIME('localtime')
On Sun, 13 Dec 2015 20:11:32 -0700 Scott Robison 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
[sqlite] Bug with DATETIME('localtime')
>... >We need a metric calendar. I propose redefining the second so that a day is >100,000 seconds long... ;) > >-- >Scott Robison And while we are already redefining the fundamental constants of measuring, we could redefine the meter to be exactly three feet and the kilogram to be exactly two pounds. I have no idea what havoc that would cause for gallons and litres, but I am quite sure musicians will be thrilled to tune their instruments to a standard of 380,16 "new hertz" ;) ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] Bug with DATETIME('localtime')
On Sun, Dec 13, 2015 at 6:44 PM, Keith Medcalf wrote: > We have been doing daylight savings changes to and from twice a year for > as long as I remember (that is more than 100 times) and we still cannot > manage to do it properly. Leap years have been occurring for a long time > and somehow we still manage to get that wrong too. Multiple timezones have > existed for a long time and we cannot get that right either. In > combination with Daylight Savings Rules, Timezones, and the want of > Politicians to fiddle with daylight savings time completely arbitrarily, > the situation is indeed grim. > As to whether they could be incorporated into SQLite, that is > interesting. Overall the manipulations are rather simple -- the problem is > the database of tz data (the IANA aka Olsen database). Of course, the data > could be stored in the database itself, but this raises the issue of having > UDFs access the database. I suppose it would not be too difficult (or too > large) to load the entire database at extension load time -- the entire > IANA database of all known timezone/transition information is just slightly > larger than 512KB... > Not a horrible size for a modern desktop operating system, but certainly not something you would want to foist onto smaller embedded systems. Speaking of which, that reminds me of something I once heard: > > "On hearing the explanation of Daylight Savings Time, the old Indian Chief > said with a shake of the head: "Only a white man could believe that > cutting a foot off the bottom of a blanket and sewing it on the top would > make the blanket any longer..."" > Amusing, though of course the intent is not make the day longer, just to save the daylight in the early morning and use it in the evening. It's a sliding window algorithm. :) I'm just glad I didn't live in the "wild west" of DST. Since 1966 DST has been pretty stable. A couple 'permanent' changes and some short term changes at specific times. Prior to 1966, individual states and localities were free to do whatever the heck they wanted, and it was confusing. I haven't looked at the IANA database in depth, but I did download it so that I could take a glance. It does include a lot of information, but it'll never be complete. Complete enough for the needs of most people, of course, who just care about adjusting UTC to legal local time for a general area, but there are places (like West Wendover, NV) that have been effectively on in the mountain time zone while legally in the pacific time zone (until they were legally moved to mountain time in 1999). If you depend on the timezone database it looks like you'll always get the "observed" time for West Wendover, but if you want the legal time, there is no rule for that. Note: I am not advocating yet more changes to the time database, especially for such a little quirk. Just observing that if they have the most complete time database (which even includes many state & locality specific rules for the US during periods that the US didn't enforce an official start and end date) but don't have everything, then it's likely impossible to ever be 100% accurate (or rather to get everyone to agree on what 100% accurate really means). We need a metric calendar. I propose redefining the second so that a day is 100,000 seconds long... ;) -- Scott Robison
[sqlite] Bug with DATETIME('localtime')
Or in python using pytz:
import datetime
import pytz
UTC = pytz.UTC
EST = pytz.timezone('Canada/Eastern')
MSK = pytz.timezone('Europe/Moscow')
tm = datetime.datetime(2014, 10, 25, 20, 0 ,0)
tm
tm = UTC.localize(tm)
tm
ms = MSK.normalize(tm.astimezone(MSK))
ms
es = EST.normalize(tm.astimezone(EST))
es
>>> import datetime
>>> import pytz
>>>
>>> UTC = pytz.UTC
>>> EST = pytz.timezone('Canada/Eastern')
>>> MSK = pytz.timezone('Europe/Moscow')
>>>
>>> tm = datetime.datetime(2014, 10, 25, 20, 0 ,0)
>>> tm
datetime.datetime(2014, 10, 25, 20, 0)
>>> tm = UTC.localize(tm)
>>> tm
datetime.datetime(2014, 10, 25, 20, 0, tzinfo=)
>>> ms = MSK.normalize(tm.astimezone(MSK))
>>> ms
datetime.datetime(2014, 10, 26, 0, 0, tzinfo=)
>>> es = EST.normalize(tm.astimezone(EST))
>>> es
datetime.datetime(2014, 10, 25, 16, 0, tzinfo=)
Or using my dt library which provides some simple wrappers and formatting
functions (uses pytz Olsen/IANA database also):
import dt
tm = dt.localize('2014-10-25 20:00:00', dt.utc)
tm
dt.convert(tm, 'Europe/Moscow')
dt.convert(tm, 'Canada/Eastern')
>>> import dt
>>> tm = dt.localize('2014-10-25 20:00:00', dt.utc)
>>> tm
datetime.datetime(2014, 10, 25, 20, 0, tzinfo=)
>>> dt.convert(tm, 'Europe/Moscow')
datetime.datetime(2014, 10, 26, 0, 0, tzinfo=)
>>> dt.convert(tm, 'Canada/Eastern')
datetime.datetime(2014, 10, 25, 16, 0, tzinfo=)
There are a number of extensions for python that make timezone manipulation
easy. pytz uses the Olsen (IANA) database. There are others that use more or
less accurate databases. Arrow is a replacement datetime which uses the
builtin datetime as a delegate. There are plans to incorporate "proper"
datetime/timezone handling as a native feature (it was delayed for the latest
python 3.5 release).
ICU also provides library functions to do proper datetime/timezone handling,
though I have not looked at it.
As you say, most TZ handling is pathetic.
We have been doing daylight savings changes to and from twice a year for as
long as I remember (that is more than 100 times) and we still cannot manage to
do it properly. Leap years have been occurring for a long time and somehow we
still manage to get that wrong too. Multiple timezones have existed for a long
time and we cannot get that right either. In combination with Daylight Savings
Rules, Timezones, and the want of Politicians to fiddle with daylight savings
time completely arbitrarily, the situation is indeed grim.
As to whether they could be incorporated into SQLite, that is interesting.
Overall the manipulations are rather simple -- the problem is the database of
tz data (the IANA aka Olsen database). Of course, the data could be stored in
the database itself, but this raises the issue of having UDFs access the
database. I suppose it would not be too difficult (or too large) to load the
entire database at extension load time -- the entire IANA database of all known
timezone/transition information is just slightly larger than 512KB...
Speaking of which, that reminds me of something I once heard:
"On hearing the explanation of Daylight Savings Time, the old Indian Chief said
with a shake of the head: "Only a white man could believe that cutting a foot
off the bottom of a blanket and sewing it on the top would make the blanket any
longer...""
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of James K. Lowden
> Sent: Sunday, 13 December, 2015 19:00
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Bug with DATETIME('localtime')
>
> On Thu, 10 Dec 2015 06:34:44 -0700
> "Keith Medcalf" wrote:
>
> > The only way to convert datetime data on windows is to use a
> > third-party package that does it properly, or write it yourself.
> > Using the WinAPI functions is equivalent to "writing it yourself"
> > because they do not actually do anything -- you have to manage all
> > the meaningful data and deal with the vagaries of the
> > under-documented closed proprietary function implementations in
> > windows (which Microsoft even admits do not work properly).
>
> Keith, your answer was so disheartening that I was impelled to revisit
> where the C standard is on time zones. I remembered it was messy, but
> thought it had surely been fixed.
>
> 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 C standard library defines functions for "local time", define
[sqlite] Bug with DATETIME('localtime')
On Sun, Dec 13, 2015 at 5:00 PM, James K. Lowden wrote: > On Thu, 10 Dec 2015 06:34:44 -0700 > "Keith Medcalf" wrote: > > > The only way to convert datetime data on windows is to use a > > third-party package that does it properly, or write it yourself. > > Using the WinAPI functions is equivalent to "writing it yourself" > > because they do not actually do anything -- you have to manage all > > the meaningful data and deal with the vagaries of the > > under-documented closed proprietary function implementations in > > windows (which Microsoft even admits do not work properly). > > Keith, your answer was so disheartening that I was impelled to revisit > where the C standard is on time zones. I remembered it was messy, but > thought it had surely been fixed. > > 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: 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. 2. Let's say that that the next version of the C standard does add TZ functionality. That functionality would almost certainly only be required for hosted implementations. Freestanding implementations have a much smaller set of requirements (they don't even require the *current* time functions!), and are the types of implementations used in targeting all these embedded devices that make SQLite (likely) the most deployed software in the world. -- Scott Robison
[sqlite] Bug with DATETIME('localtime')
On Thu, 10 Dec 2015 06:34:44 -0700
"Keith Medcalf" wrote:
> The only way to convert datetime data on windows is to use a
> third-party package that does it properly, or write it yourself.
> Using the WinAPI functions is equivalent to "writing it yourself"
> because they do not actually do anything -- you have to manage all
> the meaningful data and deal with the vagaries of the
> under-documented closed proprietary function implementations in
> windows (which Microsoft even admits do not work properly).
Keith, your answer was so disheartening that I was impelled to revisit
where the C standard is on time zones. I remembered it was messy, but
thought it had surely been fixed.
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 C standard library defines functions for "local time", defined
globally with tzset(3). To work with two time zones (even if one of
them is UTC) requires manipulating the TZ environment variable,
and "setting the time" with tzset(3) for each each one.
The latest version of the tz database[1] incorporates changes that
originated with NetBSD in 2014. NetBSD introduced some new functions
e.g. mktime_z(3) that add a timezone argument to the traditional time
functions of the C standard library. This lets you allocate a timezone
variable for each zone you're interested in, and thereby to handle the
two time zones without touching global structures.
I don't know the status of these functions beyond NetBSD. From the
docs[2] they don't seem to have yet been incorporated in GNU libc.
Perhaps they would be possible to incorporate them in SQLite?
Below are two programs -- one Posix, one NetBSD 7.0 -- that produce the
output the OP expects. Neither one requires any heavy lifiting,
although I think you'll agree the second is more interesting.
--jkl
[Posix]
#include
#include
#include
int main(int argc, char *argv[] )
{
// > 2014-10-25 20:00:00
struct tm tm = { .tm_hour = 20,
.tm_mday = 25,
.tm_mon = 9,
.tm_year = 114 };
putenv("TZ=no time like the present");
tzset();
time_t time = mktime();
printf( "time is %lu\n", (long)time );
struct tm *greenwich_tm = gmtime();
printf( "time in %s is %s",
greenwich_tm->tm_zone, asctime(greenwich_tm) );
putenv("TZ=:Europe/Moscow");
tzset();
struct tm *moscow_tm;
moscow_tm = localtime();
printf( "time in %s is %s",
moscow_tm->tm_zone, asctime(moscow_tm) );
return EXIT_SUCCESS;
}
[xisoP]
make && ./moscow
c99 -D_XOPEN_SOURCE=600 -D_BSD_SOURCE -g -o moscow main.c
time is 1414267200
time in GMT is Sat Oct 25 20:00:00 2014
time in MSK is Sun Oct 26 00:00:00 2014
[NetBSD]
#include
#include
#include
int main(int argc, char *argv[] )
{
// > 2014-10-25 20:00:00
struct tm tm = { .tm_hour = 20,
.tm_mday = 25,
.tm_mon = 9,
.tm_year = 114 };
time_t time = mktime_z(NULL, );
printf( "time is %lu\n", (long)time );
struct tm *greenwich_tm = gmtime();
timezone_t moscow_tz = tzalloc("Europe/Moscow");
const char *name = tzgetname(moscow_tz, 1);
printf("Moscow time zone name is '%s'\n", name);
struct tm *moscow_tm, data;
moscow_tm = localtime_rz(moscow_tz, , );
printf( "time in %s is %s",
greenwich_tm->tm_zone, asctime(greenwich_tm) );
printf( "time in %s is %s",
moscow_tm->tm_zone, asctime(moscow_tm) );
tzfree(moscow_tz);
return EXIT_SUCCESS;
}
[DSBteN]
make && ./moscow
c99 -g -o moscow main.c
time is 1414267200
Moscow time zone name is 'MST'
time in GMT is Sat Oct 25 20:00:00 2014
time in MSK is Sun Oct 26 00:00:00 2014
[1] https://www.iana.org/time-zones/repository/tz-link.html
[2]https://www.gnu.org/software/libc/manual/html_node/Broken_002ddown-Time.html#Broken_002ddown-Time
[sqlite] Bug with DATETIME('localtime')
In both cases the conversion is only correct when it is correct. Microsoft is
a teeny weeny company that exists and does business only in one time zone and
has existed for such a short period of time (and produces software which covers
such short periods of time) that they have never ever encountered a daylight
savings time rule change.
The only way to convert datetime data on windows is to use a third-party
package that does it properly, or write it yourself. Using the WinAPI
functions is equivalent to "writing it yourself" because they do not actually
do anything -- you have to manage all the meaningful data and deal with the
vagaries of the under-documented closed proprietary function implementations in
windows (which Microsoft even admits do not work properly).
Remarks
The SystemTimeToTzSpecificLocalTime function takes into account whether
daylight saving time (DST) is in effect for the local time to which the system
time is to be converted.
The SystemTimeToTzSpecificLocalTime function may calculate the local time
incorrectly under the following conditions:
The time zone uses a different UTC offset for the old and new years.
The UTC time to be converted and the calculated local time are in different
years.
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Vitaly Baranov
> Sent: Wednesday, 9 December, 2015 07:49
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Bug with DATETIME('localtime')
>
> Hi,
> I've found a bug with using 'localtime' in functions DATETIME(), DATE(),
> TIME().
>
> Platform: Windows 7.
> Steps to reproduce:
> 1. Set your system time zone as "Russia Time Zone 2, (UTC+03:00) Moscow,
> St. Petersburg, Volgograd)".
> 2. Execute the following script:
>
> SELECT DATETIME(1414267200, 'unixepoch');
> SELECT DATETIME(1414267200, 'unixepoch', 'localtime');
>
> Actual results:
> 2014-10-25 20:00:00
> 2014-10-25 23:00:00
>
> Expected results:
> 2014-10-25 20:00:00
> 2014-10-26 00:00:00
>
> Current local time in Moscow is "UTC+3", however it was "UTC+4" on this
> date.
>
> The following is the result of my own investigation, I hope this will be
> useful. sqlite uses C library function localtime(). However Microsoft's
> implementation of this function sometimes returns incorrect value. I
> sent this information to Microsoft (see
> https://connect.microsoft.com/VisualStudio/feedback/details/1984408 ),
> and they advised to use WinAPI instead of localtime_s(). As far as I can
> see, WinAPI function SystemTimeToTzSpecificLocalTime() works correctly
> for any date.
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug with DATETIME('localtime')
Hi, I've found a bug with using 'localtime' in functions DATETIME(), DATE(), TIME(). Platform: Windows 7. Steps to reproduce: 1. Set your system time zone as "Russia Time Zone 2, (UTC+03:00) Moscow, St. Petersburg, Volgograd)". 2. Execute the following script: SELECT DATETIME(1414267200, 'unixepoch'); SELECT DATETIME(1414267200, 'unixepoch', 'localtime'); Actual results: 2014-10-25 20:00:00 2014-10-25 23:00:00 Expected results: 2014-10-25 20:00:00 2014-10-26 00:00:00 Current local time in Moscow is "UTC+3", however it was "UTC+4" on this date. The following is the result of my own investigation, I hope this will be useful. sqlite uses C library function localtime(). However Microsoft's implementation of this function sometimes returns incorrect value. I sent this information to Microsoft (see https://connect.microsoft.com/VisualStudio/feedback/details/1984408 ), and they advised to use WinAPI instead of localtime_s(). As far as I can see, WinAPI function SystemTimeToTzSpecificLocalTime() works correctly for any date.
