local time from a GMT time OR timezone interval for winter/summer

I want to calculate the local time from a GMT time. My procedure does this considering the current date. This is OK when the input date (GMT) is in the same "winter hour"/"summer hour" as the date of execution. Trying to solve this problem I want to know either the local date for a specified GMT date (if there is a function that I can use in Informix IDS) either a function (Informix IDS) that can give me the local dates (timezone interval) when the hour change from winter to summer and from summer to winter. Thanks for any ideas.

Casian wrote: > I want to calculate the local time from a GMT time. My procedure does > this considering the current date. This is OK when the input date > (GMT) is in the same "winter hour"/"summer hour" as the date of > execution. Do you know what the local time zone is, or do you want the system to divine this for you? What constitutes a GMT time? A DATETIME YEAR TO SECOND value, or a number of seconds since the Unix (or any other) epoch? > Trying to solve this problem I want to know either the local date for > a specified GMT date (if there is a function that I can use in > Informix IDS) either a function (Informix IDS) that can give me the > local dates (timezone interval) when the hour change from winter to > summer and from summer to winter. There isn't a simple way to determine when the time changes - and the rules change continuously. The Olson database, which tries to track these things, went through at least 16 revisions (a..p) in 2006, to take into account (amongst other things), the changes in Australia to accommodate the Commonwealth Games (a one-off event) and the changes in the US rules (nominally in perpetuity, meaning until Congress changes its mind, which could happen early if the required report to Congress doesn't show the requisite savings). However, back in November, I showed how to calculate the UTC time (search at groups.google.com in comp.databases.informix for 'utc_current' in November 2006). Of course, the value of CURRENT in IDS is the local time in the server's time zone. Given utc_current() and current, you can determine the time zone offset, and hence given some other UTC (GMT) time, determine the correct local time. However, this is in the time zone of the IDS server. If you need it in an arbitrary time zone, you need to the rules about the changes - a database encoding of the rules in the Olson database. That will be trickier (and I haven't done it yet). An exact specification of what you are attempting to achieve will help. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Hi Jonathan and thanks for your response, > Do you know what the local time zone is, or do you want the system to > divine this for you? What constitutes a GMT time? A DATETIME YEAR TO > SECOND value, or a number of seconds since the Unix (or any other) epoch? > However, back in November, I showed how to calculate the UTC time > (search at groups.google.com in comp.databases.informix for > 'utc_current' in November 2006). Of course, the value of CURRENT in IDS > is the local time in the server's time zone. Given utc_current() and > current, you can determine the time zone offset, and hence given some > other UTC (GMT) time, determine the correct local time. However, this > is in the time zone of the IDS server. If you need it in an arbitrary > time zone, you need to the rules about the changes - a database encoding > of the rules in the Olson database. That will be trickier (and I > haven't done it yet). > > An exact specification of what you are attempting to achieve will help. I want to correct an anomaly on conversion into "local time" of time into "Unix format". "Unix format" is numbers of (mili) seconds since January 1, 1970. The procedure's purpose is to calculate the "local time" from the time in int8 (GMT). The problem occurs if we want to calculate the local time for a date which is not any more in the same "winter hour"/"summer hour" as the date of execution. Examples: 1 / The procedure works OK --------------------------- onstat -g env TZTZ MET -- MET means UTC + 1 hour >> the machine is in winter hour at the date of the test execute procedure TimeInt8ToDates (1170718200000);2007-02-06 00:30: 00.0 2007-02-05 23:30: 00.0 >>> effectively, there is : LOCAL time: 1170718200 translates to Tuesday, February 6th 2007, 0:30: 00 (GMT +1) GMT Hour: 1170718200 translates to Monday, February 5th 2007, 23:30: 00 (GMT) 2 / The procedure works WRONG -------------- onstat - g env TZ TZ MET -- MET means UTC + 1 hour >> the machine is in winter hour at the date of the test execute procedure TimeInt8ToDates (1180101600000);2007-05-25 15:00: 00.0 2007-05-25 14:00: 00.0 >>but we have : LOCAL time: 1180101600 translates to Friday, May 25th 2007, 16:00: 00 (GMT +2) GMT Hour : 1180101600 translates to Friday, May 25th 2007, 14:00: 00 (GMT) There is a problem because the procedure returns 15:00 instead of 16:00 for the local time. Effectively the time shift compared to hour GMT will be of 2 hours on May 25 and not only of 1 hour (as it is the date of the test). This happened because in the procedure we calculate the time zone offset for the current local time and this goes wrong when we are not in the same "winter hour"/"summer hour" as the input date.

Casian wrote: > Hi Jonathan and thanks for your response, > >> Do you know what the local time zone is, or do you want the system to >> divine this for you? What constitutes a GMT time? A DATETIME YEAR TO >> SECOND value, or a number of seconds since the Unix (or any other) epoch? > >> However, back in November, I showed how to calculate the UTC time >> (search at groups.google.com in comp.databases.informix for >> 'utc_current' in November 2006). Of course, the value of CURRENT in IDS >> is the local time in the server's time zone. Given utc_current() and >> current, you can determine the time zone offset, and hence given some >> other UTC (GMT) time, determine the correct local time. However, this >> is in the time zone of the IDS server. If you need it in an arbitrary >> time zone, you need to the rules about the changes - a database encoding >> of the rules in the Olson database. That will be trickier (and I >> haven't done it yet). >> >> An exact specification of what you are attempting to achieve will help. > > I want to correct an anomaly on conversion into "local time" of time > into "Unix format". "Unix format" is numbers of (mili) seconds since > January 1, 1970. I'm not sure where the milliseconds comes from, but I think I understand the problem - now. > The procedure's purpose is to calculate the "local time" from the time > in int8 (GMT). The problem occurs if we want to calculate the local > time for a date which is not any more in the same "winter > hour"/"summer hour" as the date of execution. So, the complaint is that all time conversions to local time occur using the current time zone offset, rather than using the time zone offset that would be in effect at the time being converted. That is, now (in winter time in the Northern Hemisphere, for sake of example), the winter time offset is applied to all conversions from UTC to local time - even when the time being converted represents a time when the time zone would be summer time. > Examples: > 1 / The procedure works OK > --------------------------- > onstat -g env TZ> TZ MET -- MET means UTC + 1 hour >>> the machine is in winter hour at the date of the test > > execute procedure TimeInt8ToDates (1170718200000); This is the first time I've heard of this procedure - and Google agrees with me that it hasn't heard of it before either. Can you explain what it is - or show its code? > 2007-02-06 00:30: 00.0 2007-02-05 23:30: 00.0 >>>> effectively, there is : > LOCAL time: 1170718200 translates to Tuesday, February 6th 2007, 0:30: > 00 (GMT +1) > GMT Hour: 1170718200 translates to Monday, February 5th 2007, 23:30: > 00 (GMT) > > 2 / The procedure works WRONG > -------------- > onstat - g env TZ > TZ MET -- MET means UTC + 1 hour >>> the machine is in winter hour at the date of the test > execute procedure TimeInt8ToDates (1180101600000);> 2007-05-25 15:00: 00.0 2007-05-25 14:00: 00.0 >>> but we have : > LOCAL time: 1180101600 translates to Friday, May 25th 2007, 16:00: 00 > (GMT +2) > GMT Hour : 1180101600 translates to Friday, May 25th 2007, 14:00: 00 > (GMT) > > There is a problem because the procedure returns 15:00 instead of > 16:00 for the local time. Effectively the time shift compared to hour > GMT will be of 2 hours on May 25 and not only of 1 hour (as it is the > date of the test). This happened because in the procedure we calculate > the time zone offset for the current local time and this goes wrong > when we are not in the same "winter hour"/"summer hour" as the input > date. I'm not at all sure there is a simple solution to this particular problem. What you need is a mechanism that, when given a time zone name and a UTC time stamp, gives you the time zone offset from UTC that applies in the named time zone at that time. And, from that, you can determine local time correctly. The best solution might well be to use the code (and data) from the Olson database - see http://www.twinsun.com/tz/tz-link.htm and ftp://elsie.nci.nih.gov/pub/ - you currently need the files tzcode2007a.tar.gz and tzdata2007a.tar.gz. However, there is a good deal of non-trivial work involved in understanding those functions, and the data, and I'm not sure which of the functions does exactly what you need. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/