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

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/

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. "Jonathan Leffler" <jleffler@earthlink.net> wrote in message news:1pF1b.126$3E.66@newsread3.news.pas.earthlink.net... > 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/ >

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: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/