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 Eastern US time zone.
How would you suggest I do this?
Thanks very much!
RobR
