|
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 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 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 |
