Access to UTC time with servers running in local time

[Michael Mueller]

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: [url]http://www.mm.kay-mueller.de[/url]
[url]http://www.planets.kay-mueller.de[/url]
======================================

sending to informix-list

[Michael Mueller]

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: [url]http://www.mm.kay-mueller.de[/url]
[url]http://www.planets.kay-mueller.de[/url]
======================================

sending to informix-list

[Jonathan Leffler]

Michael Mueller wrote:

> 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).

I wasn't aware of the get_tz option - I live, I learn. I'm not sure
it is all that much use, but that's a separate discussion.

The dbinfo('utc_current') information (which I'd forgotten about)
points the way to do it in the server, though. There's an SPL
function in the IIUG Software Archive with a name like
unixtime_to_datetime. It converts an integer number of seconds since
the Epoch (1970-01-01 00:00:00 +00:00) into a datetime year to second
- in UTC. You can also call CURRENT YEAR TO SECOND to get the
server's local time. The difference - an interval DAY(n) TO SECOND -
is the time zone the server is running in; that's the information we
wanted. About the only thing to watch, IIRC, is that the value of
dbinfo('utc_current') varies during the execution of a statement, but
the value of CURRENT does not. If your statement is long running,
that could skew the difference. If you want an INTERVAL HOUR TO
MINUTE, then you need to add the subtraction to 'INTERVAL(0:0) HOUR TO
MINUTE' - and worry about rounding. The zero interval must be on the
LHS of the addition. If you want a different interval (e.g. INTERVAL
MINUTES(4) TO MINUTE), change the constant.

Thanks for the help, 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.
> >>

> >
> >

>
>

--
Jonathan Leffler #include <disclaimer.h>
Email: [email]jleffler@earthlink.net[/email], [email]jleffler@us.ibm.com[/email]
Guardian of DBD::Informix v2003.04 -- [url]http://dbi.perl.org/[/url]