How do i calculate a finish time when the start time is ambiguous?

From: "Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: How do i calculate a finish time when the start time is ambiguous?
Date: 2011-03-28 15:26:03
Message-ID: 04A6DB42D2BA534FAC77B90562A6A03D017CC3E0@server.rad-con.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Richardson 2011-03-28 15:45:26 A simpler time zone question
Previous Message Vibhor Kumar 2011-03-28 14:24:56 Re: Postgres 9 silent installation on Windows