CODEWITHSUNDEEP
postgresqltimezonedst
Konrad Garus
Konrad Garus

Reputation: 54045

Can Postgres differentiate timestamps with DST?

On 11/4/12 in the USA clocks shifted from 2AM to 1AM for the winter time. For instance, 2AM CDT became 1AM CST.

It means that on that 1:32 AM "occurred two times": 1:32 CDT (epoch 1352010776642), and an hour later 1:32 CST (epoch 1352014376642).

Is it possible to somehow differentiate the two in plain timestamp type in PostgreSQL? What we observed is that at 1:32 CDT our application stored dates as 1352014376642 (the "second occurrence").

Upvotes: 4

Views: 1272

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325141

Not as far as I know, no.

TIMESTAMP WITHOUT TIME ZONE ("plain timestamp) like you've used stores the local time directly, again without storing an associated UTC offset or time zone. It's local time, so unless you stored the time zone associated with that local time, it could be one of many different instants.

There is no way to differentiate '2012-01-01 11:00 +0800' from '2012-01-01 11:00 +0700' once converted to timestamptz and stored. So if you have a DST shift cause an hour to replay in a different time zone, you cannot reconstruct that information. Witness:

regress=> select extract(epoch from '2012-01-01 11:00 +0800'::timestamp),
                 extract(epoch from '2012-01-01 11:00 +700'::timestamp);
 date_part  | date_part  
------------+------------
 1325415600 | 1325415600
(1 row)

As you can see, the timezone is ignored; it is stripped and discarded. timestamp fields are not the right type to use to identify discrete points in time, so you're SOL.

BTW, TIMESTAMP WITH TIME ZONE converts the timestamp to UTC for storage and back to the local time zone for retrieval, using the timezone setting. It describes a single instant (roughly, see link at the end). This means that in timestamptz, like timestamp the original time zone is lost. This is confusing and appears to contradict the name of the data type. That's how the standard is, apparently, so we're stuck with it whether or not it's stupid. To differentiate the timestamps you'd also need to have stored the associated UTC offset. It'd be better named TIMESTAMP WITH TIME ZONE CONVERSION.

That makes timestamptz good for storing discrete points in time, but not much good for storing when an event happened in real-world local time. Store the UTC offset and/or the tzname as well.

See:

regress=> select extract(epoch from '2012-01-01 01:00 CST'::timestamptz),
                 extract(epoch from '2012-01-01 02:00 CDT'::timestamptz);
 date_part  | date_part  
------------+------------
 1325401200 | 1325401200

There is, sadly, no data type that combines TIMESTAMP WITH TIME ZONE with an internal UTC offset recording the TZ offset before conversion.

That said, you can't rely on the clock not to double up timestamps or otherwise be bizarre anyway, so it's necessary to have code that's very robust about time and doesn't trust it to make much sense.

Upvotes: 2

Related Questions