Datetime storage problem

Hi there, I'm running a number of 10.0.UC3 instances on RHEL4 There are a number of database tables that have a datetime column in them. At the moment when records get inserted into this column they go in with the CURRENT keyword. The application delivering data has the capacity to use the UTC time instead of my localtime as returned by CURRENT (New Zealand time) The problem I have is around daylight savings - using localtime causes me to have an hour's gap in the data at one end of the year and an hours overlap at the other. To get around this I want to convert the system to use UTC. Now I understand that the database columns don't care what time I put in them but what I was wondering was if there was a set of SPL routines / commands that would allow me to convert the UTC time into a local time at time of query. things like a local_time routine to let the UTC dates be compared to CURRENT. SELECT local_time(sample_dt) FROM table WHERE local_time(sample_dt) > CURRENT - 1 UNITS DAY The bulk of the applications reporting the data are in Perl, so I can use Date::Calc to convert in there if I have to, but it would be much cleaner to be able to build a "localtime" VIEW of the table for querying rather than directly accessing the UTC dates. I could always rebuild the view with "sample_dt + 12 units hour" for six months of the year and "sample_dt + 11 units hour" for the other six, but this isn't as elegant as I'd like. Any suggestions welcome. Jarrod Teale DISCLAIMER: This email contains confidential information and may be legally privileged. If you are not the intended recipient or have received this email in error, please notify the sender immediately and destroy this email. You may not use, disclose or copy this email or its attachments in any way. Any opinions expressed in this email are those of the author and are not necessarily those of the Fonterra Co-operative Group. http://www.fonterra.com/

Jarrod Teale wrote: > Hi there, > I'm running a number of 10.0.UC3 instances on RHEL4 > > There are a number of database tables that have a datetime column in > them. At the moment when records get inserted into this column they go > in with the CURRENT keyword. > The application delivering data has the capacity to use the UTC time > instead of my localtime as returned by CURRENT (New Zealand time) > The problem I have is around daylight savings - using localtime causes > me to have an hour's gap in the data at one end of the year and an hours > overlap at the other. > To get around this I want to convert the system to use UTC. > > Now I understand that the database columns don't care what time I put in > them but what I was wondering was if there was a set of SPL routines / > commands that would allow me to convert the UTC time into a local time > at time of query. things like a local_time routine to let the UTC dates > be compared to CURRENT. > > SELECT local_time(sample_dt) FROM table WHERE > local_time(sample_dt) > CURRENT - 1 UNITS DAY > > The bulk of the applications reporting the data are in Perl, so I can > use Date::Calc to convert in there if I have to, but it would be much > cleaner to be able to build a "localtime" VIEW of the table for querying > rather than directly accessing the UTC dates. I could always rebuild the > view with "sample_dt + 12 units hour" for six months of the year and > "sample_dt + 11 units hour" for the other six, but this isn't as elegant > as I'd like. Suggestion: launch oninit with TZ=UTC0 in the environment. That way, CURRENT evaluated in the server will always be UTC, no timezone switches. I run my instances of IDS like this, though not for this reason. Obvious downside: you have to convert to localtime - you also have to ensure the server is always started with the same environment. For conversion to local time, do you want the conversion to be done 'using the time zone in effect at the UTC time represented in the database' (hard but most nearly correct), or 'using the time zone currently in effect' (easier, but not so correct). For the easy one, you simply deduce the current time zone offset - I've shown how to do that before in this group, but I'll go digging for the answer if you can't find it. For the hard one, I don't have a solution on hand. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/

Thanks for that. The solution proposed may cause more problems that it will solve however as there are some 35 databases running in this instance, and the applications that control the other 34 won't appreciate me just changing their time zone. As for the conversion, it would have to be the hard option. I'd need to convert to the time zone at the time expressed by UTC. Looks like a Perl solution is in my future... Jarrod -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Jonathan Leffler Sent: Thursday, 12 July 2007 4:22 p.m. To: informix-list@iiug.org Subject: Re: Datetime storage problem Jarrod Teale wrote: > Hi there, > I'm running a number of 10.0.UC3 instances on RHEL4 > > There are a number of database tables that have a datetime column in > them. At the moment when records get inserted into this column they go > in with the CURRENT keyword. > The application delivering data has the capacity to use the UTC time > instead of my localtime as returned by CURRENT (New Zealand time) The > problem I have is around daylight savings - using localtime causes me > to have an hour's gap in the data at one end of the year and an hours > overlap at the other. > To get around this I want to convert the system to use UTC. > > Now I understand that the database columns don't care what time I put > in them but what I was wondering was if there was a set of SPL > routines / commands that would allow me to convert the UTC time into a > local time at time of query. things like a local_time routine to let > the UTC dates be compared to CURRENT. > > SELECT local_time(sample_dt) FROM table WHERE > local_time(sample_dt) > CURRENT - 1 UNITS DAY > > The bulk of the applications reporting the data are in Perl, so I can > use Date::Calc to convert in there if I have to, but it would be much > cleaner to be able to build a "localtime" VIEW of the table for > querying rather than directly accessing the UTC dates. I could always > rebuild the view with "sample_dt + 12 units hour" for six months of > the year and "sample_dt + 11 units hour" for the other six, but this > isn't as elegant as I'd like. Suggestion: launch oninit with TZ=UTC0 in the environment. That way, CURRENT evaluated in the server will always be UTC, no timezone switches. I run my instances of IDS like this, though not for this reason. Obvious downside: you have to convert to localtime - you also have to ensure the server is always started with the same environment. For conversion to local time, do you want the conversion to be done 'using the time zone in effect at the UTC time represented in the database' (hard but most nearly correct), or 'using the time zone currently in effect' (easier, but not so correct). For the easy one, you simply deduce the current time zone offset - I've shown how to do that before in this group, but I'll go digging for the answer if you can't find it. For the hard one, I don't have a solution on hand. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/ _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list DISCLAIMER: This email contains confidential information and may be legally privileged. If you are not the intended recipient or have received this email in error, please notify the sender immediately and destroy this email. You may not use, disclose or copy this email or its attachments in any way. Any opinions expressed in this email are those of the author and are not necessarily those of the Fonterra Co-operative Group. http://www.fonterra.com/