Greetings!
Our application stores the times at which several events happened, and we need to be able to calculate the elapsed time between events. Currently, the times are stored as timestamps without time zone, in both local and UTC times. Elapsed time calculations are based on the UTC times. Supposedly, that should avoid problems posed by the change from standard to daylight savings time, but it is not working out that easily in practice.
I thought that I could convert the non-timestamped value to a UTC time by using the “AT TIME ZONE” clause, but I see now that that is still using the local time zone. I want to convert '2010-03-14 12:00 CDT' to UTC, but the result I get from
select into UTCTimestampTZ CurrentTimestampTZ at time zone 'UTC';
is
NOTICE: UTCTimestampTZ: 2010-03-14 17:00:00-04
(The example time came from someone in Indiana, in the Central time zone, and I’m in the Eastern time zone.)
The result I want is 2010-03-14 17:00:00-00. Is there any way to get that?
But then, in another forum, I found this:
I do realize that currently timestamptz doesn't store the timezone
offset/timezone name (thus timestamp and timestamptz both require the
same amount of storage, 8 bytes).
I didn’t realize that. If that’s true, then the time zone is basically meaningless, only telling the user what his current time zone is.
I had thought that it would be good for us to modify our application to use only timestamped values. Then, we’d get an event startong at 2010-3-13 12:00-05 (before daylight savings time in the Eastern time zone) and ending at 2010-3-14 12:00-04, and PostgreSQL would correctly see the difference in time zones and report the difference as 23 hours (since we sprang ahead in the spring time change). But if PostgreSQL doesn’t store time zones internally, then that difference is going to be 24 hours, which doesn’t help me.
So what is the best way to calculate the elapsed time between two times spanning a change from standard to daylight savings time, or the reverse?
Thank you very much.
RobR