Postgres saves 'timestamp with time zone' with default time zone-CodePudding

I have a table in Postgres (13.3):

create table owner (date_time timestamp with time zone);

I've saved a record with time zone:

insert into owner(date_time) values (timestamp with time zone '2010-10-10 10:10:10.000000  03:00');

I expect that the value is saved with timezone I defined, but the actual value is:

2010-10-10 07:10:10.000000 00:00

CodePudding user response：

The name timestamp with time zone is perhaps unfortunate, but it's been that way for decades and we are stuck with it now.

It might better be named absolute timestamp and what it actually stores is a timestamp in UTC. What it actually displays is a timestamp in your client's defined timezone.

richard=> SELECT CURRENT_TIMESTAMP;
┌───────────────────────────────┐
│       current_timestamp       │
├───────────────────────────────┤
│ 2022-11-22 14:03:57.919421 00 │
└───────────────────────────────┘
(1 row)

richard=> SET timezone = 'Europe/Paris';
SET
richard=> SELECT CURRENT_TIMESTAMP;
┌───────────────────────────────┐
│       current_timestamp       │
├───────────────────────────────┤
│ 2022-11-22 15:04:10.943315 01 │
└───────────────────────────────┘
(1 row)

If you actually want to maintain the timezone offset you will either need to separately store the offset time, or the zone name (e.g. "Europe/Paris").

Note that they are NOT the same in zones which have DST.