|
Greetings! I have a table that records a starting time for a process
and the length of time that process will take, and I need to calculate the time
the process will end. I have the starting time both in local time and in
UTC time, but for reasons which I consider totally idiotic, they are timestamp
fields, not timestamptz fields. My calculation must take into account the
possibility that time will change from standard to daylight time or vice versa
during the interval. For all times except one hour once a year, the task is
trivial. I merely declare a timestamptz variable in my function and set
it equal to the stored timestamp value, add the required time, and store the
result in a timestamptz variable. But for the one hour before the fall time change, I do not
know if the stored local time is daylight or standard time, since at 2:00 AM on
the day of the change, time changes back to 1:00 AM. But since I do have
the UTC time (without time zone), I can extract the timezone offset by
subtracting the UTC time from the local time. But I’m having a difficult time coming up with a
function that will use this information. In the Eastern US time zone: finishtime(‘2010-11-7 1:30’, ‘2010-11-7
5:30’, 120) should return ‘2010-11-7 2:30’, since the time
change happened at 2 AM on November 7th, 2010, and the difference
between the wallclock and UTC times is 4 hours, which indicates that the two
times were stored during daylight savings time. But: finishtime(‘2010-11-7 1:30’, ‘2010-11-7 6:30’,
120) should return ‘2010-11-7 3:30’, since the five-hour difference
between the times indicates that the time changed had already occurred. I would like this function to be general enough that it will
work in any time zone for which a one-hour time change occurs for daylight
savings time, rather than hard-coding the offsets for the How would you suggest I do this? Thanks very much! RobR |
