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=<UTC>)
>>> ms = MSK.normalize(tm.astimezone(MSK))
>>> ms
datetime.datetime(2014, 10, 26, 0, 0, tzinfo=<DstTzInfo 'Europe/Moscow' 
MSK+4:00:00 STD>)
>>> es = EST.normalize(tm.astimezone(EST))
>>> es
datetime.datetime(2014, 10, 25, 16, 0, tzinfo=<DstTzInfo 'Canada/Eastern' EDT-1 
day, 20:00:00 DST>)


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=<UTC>)
>>> dt.convert(tm, 'Europe/Moscow')
datetime.datetime(2014, 10, 26, 0, 0, tzinfo=<DstTzInfo 'Europe/Moscow' 
MSK+4:00:00 STD>)
>>> dt.convert(tm, 'Canada/Eastern')
datetime.datetime(2014, 10, 25, 16, 0, tzinfo=<DstTzInfo 'Canada/Eastern' EDT-1 
day, 20:00:00 DST>)

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" <kmedcalf at dessus.com> 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 <time.h>
> #include <stdlib.h>
> #include <stdio.h>
> 
> 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(&tm);
>   printf( "time is %lu\n", (long)time );
> 
>   struct tm *greenwich_tm = gmtime(&time);
> 
>   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(&time);
> 
>   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 <time.h>
> #include <stdlib.h>
> #include <stdio.h>
> 
> 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, &tm);
>   printf( "time is %lu\n", (long)time );
> 
>   struct tm *greenwich_tm = gmtime(&time);
> 
>   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, &time, &data);
> 
>   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-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



Reply via email to