Can Postgres differentiate timestamps with DST?

Question

On 11/4/12 in the USA, the clock moved from 2 a.m. to 1 a.m. in winter. For example, 2AM CDT became 1AM CST.

This means that at 1:32 a.m. “twice happened”: 1:32 CDT (era 1352010776642), and an hour later 1:32 CST (era 1352014376642).

Is there any way to distinguish the two in the regular timestamp type in PostgreSQL? We noticed that in 1:32 CDT our application is stored as 1352014376642 ("second occurrence").

Answer 1

As far as I know, no.

TIMESTAMP WITHOUT TIME ZONE ("plain timestamp ), as you used, saves local time directly without saving the associated UTC offset or time zone. This is local time, so if you did not save the time zone associated with this local time, this may to be one of many different moments.

It is impossible to distinguish between '2012-01-01 11:00 +0800' and '2012-01-01 11:00 +0700' after conversion to timestamptz and saved. Therefore, if you have a DST shift, you can transfer the clock to another time zone, you will not be able to restore this 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 time zone is ignored; he is lost and discarded. The timestamp fields are not suitable for identifying discrete points in time, so you are 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 parameter. It describes one instant (for approximately, see Link at the end). This means that in timestamptz , such as timestamp , the original time zone is lost. This is confusing and apparently contradicts the name of the data type. This is apparently the standard, so we are stuck in it whether it is stupid or not. To distinguish between timestamps, you also need to save the corresponding UTC offset. It would be better named TIMESTAMP WITH TIME ZONE CONVERSION .

This makes timestamptz good for storing discrete points in time, but not very useful for storing when an event occurred in real time in real time. Save the UTC offset and / or tz name.

Cm:

 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 

Unfortunately, there is no data type that combines TIMESTAMP WITH TIME ZONE with an internal UTC offset that records the TZ offset before the conversion.

However, you cannot rely on watches to not double timestamps or otherwise be fancy anyway, so you must have a code that is a very reliable time and does not trust it in order to make sense.