Thoroughly confused about time zones

From: "Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Thoroughly confused about time zones
Date: 2011-02-28 15:06:28
Message-ID: 04A6DB42D2BA534FAC77B90562A6A03D01769648@server.rad-con.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rob Richardson 2011-02-28 15:18:35 Re: Thoroughly confused about time zones
Previous Message Merlin Moncure 2011-02-28 15:00:39 Re: Hot Standby - ERROR: canceling statement due to conflict with recovery