Re: Access to UTC time with servers running in local time

Just for the sake of completeness: There also is a dbinfo( 'get_tz') which behaves a bit funny. If the TZ environment variable in the client is not set it returns the server's string value for TZ. If TZ is set in the client, it returns the client's own value. If your server uses TZ to set the time zone, you can get it this way. But many operating systems have other means than TZ to set the time zone system wide (but may also support TZ settings). Michael Michael Mueller wrote: > There is an undocumented way of getting the server's utc time in seconds > just like the time() system call on the server would return it: > > select dbinfo( 'utc_current') from systables where tabid = 99 > > (There also is a dbinfo('utc_to_datetime', <some_utc_int>) to convert it > to a string. But unfortunately it uses local time). > > If you want it in ascii and are willing to use esqlc, you could do this: > > #include <time.h> > > main() > { > struct tm *tm; > > exec sql begin declare section; > int tim; > exec sql end declare section; > > exec sql whenever error stop; > exec sql database sysmaster; > > exec sql select dbinfo( 'utc_current') into :tim > from systables where tabid = 99; > printf("utc time %d\\n", tim); > tm = gmtime((time_t *)&tim); > printf("%s\\n", asctime(tm)); > } > > > Michael > > > > Jonathan Leffler wrote: > >> Pablo wrote: >> >> >>> I am running server under Linux. >>> >>> Can I get the UTC time in servers with SQL sentences ? >>> >>> The 'current' and 'today' options always return local time, this >>> server has several databases with differents time zones and I need to >>> obtain UTC time, set the environment variable TZ=UTC+0 is not possible >>> due to I'm not DBA. >> >> >> >> >> I've scratched my head on this, and I don't think it can be done >> trivially. If you have a programming language (eg I4GL) and you know >> your own time zone offset from UTC, then you can do it by calculating: >> >> 1. In your program, find your local current time. >> 2. Given that and your time zone offset, calculate the current UTC. >> 3. Get the server to tell you what it thinks the time is: >> SELECT CURRENT YEAR TO SECOND FROM SysTables WHERE Tabid = 1; >> 4. Use that and the current UTC to determine the server's time zone. >> >> With that in place, you can now get the server to calculate the UTC >> for you. Remember that the machines may not be synchronized with NTP >> or SNTP, so allow for drifting clocks. >> >> It's simpler simply to know what the server's time zone is. >> >> If you only have DB-Access, then the only way to do it, I think, is to >> know what the server's time zone is -- or know what UTC is on your >> client-side. Actually, that can be done pretty simply; you can >> conflate steps 1 and 2 if your time zone is (temporarily) UTC; run >> your program with TZ=UTC0 in the environment. Beware 'spring forward, >> fall back', as they say here in the USA. >> > > -- === Michael Mueller ================== Tel. + 49 8171 63600 Fax. + 49 8171 63615 Web: http://www.mm.kay-mueller.de http://www.planets.kay-mueller.de ====================================== sending to informix-list