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