Re: Access to UTC time with servers running in local time
Posted in 2003
Topics: Connectivity: ESQL/C, 4GL & Embedded SQL, Server Administration
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