Access to UTC time with severs running in local time

[Pablo]

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.


Thanks you.

[Jonathan Leffler]

Catfish wrote:

> Why not write a stored procedure? You can run 'SYSTEM date -u' and
> add your format commands or run a short script. The 'date -u'
> command returns GMT. I've been doing something like this in my
> procs for years as procs return the start and not current time (in
> 7.31). If required, have the proc return the time then you can run
> it in your SQL code.

The only issue there is how do you get the value from the system
command into the database server? There is no way to retrieve the
information directly in the SP, so you would have to insert it into a
table. Since the insertion would be done by a separate process, you
can't use a temp table - it has to be a permanent table. And then you
start running into a variety of concurrency issues. How do you ensure
you get the correct time?

Yes, it can be done that way. I don't think it is all that easy,
though. OTOH, it does stay firmly on the server, so it is probably
easier than what I was outlining.

> "Jonathan Leffler" <jleffler@earthlink.net> 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]

[Catfish]

"Jonathan Leffler" <jleffler@earthlink.net> wrote in message
news:hPf2b.2252$Jh2.1424@newsread4.news.pas.earthl ink.net...

> Catfish wrote:

> > Why not write a stored procedure? You can run 'SYSTEM date -u' and
> > add your format commands or run a short script. The 'date -u'
> > command returns GMT. I've been doing something like this in my
> > procs for years as procs return the start and not current time (in
> > 7.31). If required, have the proc return the time then you can run
> > it in your SQL code.

>
> The only issue there is how do you get the value from the system
> command into the database server? There is no way to retrieve the
> information directly in the SP, so you would have to insert it into a
> table. Since the insertion would be done by a separate process, you
> can't use a temp table - it has to be a permanent table. And then you
> start running into a variety of concurrency issues. How do you ensure
> you get the correct time?
>
> Yes, it can be done that way. I don't think it is all that easy,
> though. OTOH, it does stay firmly on the server, so it is probably
> easier than what I was outlining.

It's not hard. Just how bad do you need this time.
You're correct about using a table to transfer the datetime.
Here's the code. However, embedding the proc in SQL is not for large reads.
For the large reads, store the datetime in a temp table.
================================================== ======
## /opt/informix/scripts/gmt_dtime.sh
#!/bin/sh
GMTdtime=`date -u "+%Y-%m-%d %H:%M:%S"`
dbaccess workdb - <<! > /dev/null 2>&1
set lock mode to wait;
update wk_gmtdtime set gmtdtime = "$GMTdtime";
!
================================================== ======
create table wk_gmtdtime (gmtdtime datetime year to second);

insert into wk_gmtdtime values (current);

create procedure sp_gmtdtime()
returning datetime year to second;
define v_gmtdtime datetime year to second;
system "/opt/informix/scripts/gmt_dtime.sh";
select gmtdtime into v_gmtdtime from wk_gmtdtime;
return v_gmtdtime;
end procedure;

select sp_gmtdtime() gmt from systables where tabid = 1;
================================================== ======

> > "Jonathan Leffler" <jleffler@earthlink.net> 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]
>