postgres - Display timestamp column with and without TIMEZONE paramter setting

Question

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.

Chris Bandy · Accepted Answer · 2018-05-24 05:26:35Z

1

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.

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.
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.
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.)

answered May 24, 2018 at 5:26

Chris Bandy

1,61814 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

cogitoergosum Over a year ago

With timestamptz, did you mean TIMESTAMP WITH TIME ZONE? Because, I am using the column data type as TIMESTAMP. In which case, the setting of TIME ZONE parameter is not applicable?

Chris Bandy Over a year ago

Yep! > timestamptz is accepted as an abbreviation for timestamp with time zone; this is a PostgreSQL extension.

Chris Bandy Over a year ago

You can experiment with the behavior of different combinations using type casts: SELECT 'some value'::timestamp, 'some value'::timestamptz

Collectives™ on Stack Overflow

postgres - Display timestamp column with and without TIMEZONE paramter setting

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related