CODEWITHSUNDEEP
postgresql
Tom
Tom

Reputation: 6342

How to specify time zone for the time stamp column when creating table in PostgreSql

I am creating a table with timestamp column,I am stuggling with time zone settings, I want to specify the time zone on the column,as follows:

create table t1(a date, b timestamp with time zone 'America/Los_Angeles', c timestamp without time zone)

But the grammar is wrong, I would ask how to specify the time zone on the column, than

Upvotes: 0

Views: 1308

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 339917

👉 You do not specify a time zone when defining the column.

CREATE TABLE t1
(
    a DATE ,
    b TIMESTAMP WITH TIME ZONE ,
    c TIMESTAMP WITHOUT TIME ZONE
)
;

You need to read the documentation carefully. Programming by intuition tends to end badly.

The TIMESTAMP WITH TIME ZONE type in Postgres does not save a time zone. The type uses any offset or time zone info supplied with an input to adjust to an offset of zero hours-minutes-seconds from UTC. 👉 Every value in that column is set to an offset of zero. After adjusting to zero offset, the supplied time zone or offset info is discarded by Postgres.

If you care about the original time zone, you need to write that value into a separate column yourself.

In contrast, the TIMESTAMP WITHOUT TIME ZONE type lacks any concept of a time zone or offset from UTC. A column of this type stores simply a date and a time-of-day. So values in this column cannot represent a moment, cannot refer to a specific point on the timeline. If you write noon on the 23rd of last January, we have no way of knowing if you meant noon in Tokyo Japan, noon in Toulouse France, or noon in Toledo Ohio US. Those would be three different moments, several hours apart.

Some other databases share the same behavior as Postgres. But not all. The SQL standard barely touches on the subject of date-time, just mentioning the types but without much detail regarding prescribed behavior. As a consequence, date-time behavior varies widely across database engines.

I should mention that some tools have an anti-feature where they inject a default time zone, used to adjust a value stored in UTC to that zone. pgAdmin is, unfortunately, one such tool. While well-intentioned as a convenience to the user, this behavior creates the illusion of a time zone having been stored and retrieved. I would rather all tools “tell the truth”, and report retrieved values with an offset of zero. A workaround is to set the current default time zone of your database session to UTC.

All this has been covered many times already here on Stack Overflow, and also on the sister site https://dba.stackexchange.com/. Search to learn more.

Upvotes: 2

Related Questions