CODEWITHSUNDEEP
postgresqldatetimetimezonetimestamp
cogitoergosum
cogitoergosum

Reputation: 2481

postgres - Display timestamp column with and without TIMEZONE paramter setting

To begin with, the official document says the following:

To address these difficulties, we recommend using date/time types that contain both date and time when using time zones. We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard). PostgreSQL assumes your local time zone for any type containing only date or time.

And on display of time stamp data, it says:

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

Therefore, I have defined columns as timestamp data type and inserting into the table as "2018-05-24 09:30:00+05:30". Based on the points mentioned above, I was hoping to see time stamp (in SELECT query output) in UTC i.e. 2018-05-24 04:00:00 because TIME ZONE was set to UTC. And, when I change the parameter as

SET TIME ZONE INTERVAL '+05:30' HOUR TO MINUTE;

I was hoping to see 2018-05-24 09:30:00. But, regardless of the time zone parameter, I see query output as 2018-05-24 09:30:00. How does this observation tally with the documentation?

The insert to the database was done with Javascript whereas other operations were done with psql.

Upvotes: 1

Views: 195

Answers (1)

Chris Bandy
Chris Bandy

Reputation: 1608

The critical phrasing in the documentation is "timezone-aware dates and times". This is referring to the timestamptz type.

Rephrasing the docs: When you want timezones to be happening during input/output, use timestamptz type.

  1. This type is rendered/output in the session time zone. That is, all values will be offset from UTC and be shown with a zone suffix, e.g. +05:30.
  2. Literals without any zone information are interpreted/input as being in the session time zone. That is, 2018-05-24 09:30:00 will act as though it has the suffix +05:30.
  3. Zone information in literals are considered rather than discarded. That is, 2018-05-24 09:30:00+05:30 will be converted to 04:00 UTC.

(The input side seems a little magic, but it's because literals are untyped until they are cast or get used with something typed. That is, '2018-05-24 09:30:00+05:30' by itself is neither timestamp nor timestamptz. It's a pile of characters that are interpreted when you insert into a table or compare to a column.)

Upvotes: 1

Related Questions