I want to update a date with a timezone ( 2 hours) but it ends up as UTC (0 hours)

Date type is 'timestamp-with-timezone'

Query...

update table set date = '2022-05-25 13:28 02:00'

will end up as this in the database.

2022-05-25 11:28:00 00

What's wrong here?

CodePudding user response：

tl;dr

Nothing wrong. Postgres stores values of TIMESTAMP WITH TIME ZONE in UTC, always an offset from UTC of zero. Any submitted offset or zone is used to adjust to UTC.

Details

Date type is 'timestamp-with-timezone'

No such type in standard SQL, nor in Postgres.

I’ll assume you meant TIMESTAMP WITH TIME ZONE.

it ends up as UTC (0 hours)

Read the fine manual. You are seeing documented behavior.

Postgres always stores values in a column of type TIMESTAMP WITH TIME ZONE in UTC, that is, with an offset of zero hours-minutes-seconds.

Any time zone or offset provided with an input is used to adjust into UTC. That provided zone or offset is then discarded.

So the name of the type TIMESTAMP WITH TIME ZONE is a misnomer. First, the authors of the SQL were thinking in terms of offset, not real time zones. Second, any submitted time zone is not stored. A submitted zone is used to adjust and then discarded.

If you need to track the original offset or zone, add an extra column. You’ll have to add code to store the offset amount or the time zone name.

update table set date = '2022-05-25 13:28 02:00' will end up as this in the database. 2022-05-25 11:28:00 00 What's wrong here?

Nothing is wrong. That is a feature, not a bug. Both of those strings represent the very same simultaneous moment.