CODEWITHSUNDEEP
sqlpostgresqldatetimestamp-with-timezone
Jingle
Jingle

Reputation: 673

How to update a date with a time zone in postgresql?

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?

Upvotes: 0

Views: 5012

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 339858

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.

FYI, database engines vary widely in their behavior handling date-time types and behaviors.

Some do as Postgres does regarding TIMESTAMP WITH TIME ZONE, adjusting to UTC and then discarding any provided time zone or offset. Some others may not.

The SQL standard barely touches on the topic of date-time handling. It declares a few types, and does that poorly with incomplete coverage of all cases. And the standard neglects to define behavior.

So, be very careful when it comes to date-time handling in your database work. Read very carefully the documentation for your particular database engine. Do not make assumptions. Run experiments to validate your understanding. And know that writing portable SQL code for date-time may not be feasible.

Upvotes: 2

Related Questions