Reputation: 54045
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
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