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

"Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:hPf2b.2252$Jh2.1424@newsread4.news.pas.earthlink.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: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/ >