UTC, time zone support for datetimes

After digging through the documentation, I was surprised to find no support for times zones in datetime data. For example, if I run: insert into mytable (when) values (current year to secord) I get the local time with no mention of time zone. So comparing values in mytable.when would be unreliable, since local time may not be continuous. Is there any way of asking the database for (the equivalent of) the current time in UTC? (It is essential to use the database's clock to avoid skew among applications using the database.) Thank you, Andrew Email Cc:'s appreciated.

Andrew Pimlott wrote: > After digging through the documentation, I was surprised to find > no support for times zones in datetime data. For example, if I run: > > insert into mytable (when) values (current year to secord)> > I get the local time with no mention of time zone. So comparing > values in mytable.when would be unreliable, since local time may > not be continuous. Yes. I found this out, way back when I living in the GMT timezone. > Is there any way of asking the database for (the equivalent of) > the current time in UTC? (It is essential to use the database's > clock to avoid skew among applications using the database.) Not easily. You have to work some magic on the timezone to determine the current offset to UTC, and then manually apply that correction. SQL-92 has DATETIME values with timezone appendages, but not at the entry level. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

In article <36C3C021.42D8@earthlink.net>, Jonathan Leffler wrote: >> Is there any way of asking the database for (the equivalent of) >> the current time in UTC? (It is essential to use the database's >> clock to avoid skew among applications using the database.) > >Not easily. You have to work some magic on the timezone to determine >the current offset to UTC, and then manually apply that correction. You don't mean you can get the offset from the database, do you? I assume you're suggesting figuring out the offset in the application. There will of course still be a brief window when you get it wrong, but at least it's less than an hour. If you mean I can get the database's idea of the offset (simultaneous to getting it's idea of the time), let me know how. >SQL-92 has DATETIME values with timezone appendages, but not at the >entry level. I know--I keep two SQL-92 books next to me because I need to write as-portable-as-possible database code. Most of what's in those books isn't in any database I've seen, after six years. >Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) >Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN ^^^^^^^^^^^^^ Wow, you're everywhere! I have a DBD::Informix question, but I'll take it to the right list.... Andrew -- "It's like a love-hate relationship, without the love" - Jamie Zawinski, consummate UNIX hater, on Linux