CODEWITHSUNDEEP
postgresqldatetimezonetimestamp
Federico
Federico

Reputation: 3920

Different uses of at time zone statement in Postgres?

I encountered the following SQL statement which had this strange use of at time zone.

select timestamp '2000-01-01 12:00:00' at time zone 'utc+5' at time zone 'utc+5';

The documentation didn't help much, but by the following examples, it seems that at time zone can be used in two different ways:

Examples (assuming the local time zone is PST8PDT):

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';

Result: 2001-02-16 19:38:40-08

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';

Result: 2001-02-16 18:38:40

The first example takes a time stamp without time zone and interprets it as MST time (UTC-7), which is then converted to PST (UTC-8) for display. The second example takes a time stamp specified in EST (UTC-5) and converts it to local time in MST (UTC-7).

Upvotes: 0

Views: 42

Answers (1)

Federico
Federico

Reputation: 3920

One way to understand this is by thinking of at time zone as having two different uses, one that modifies the inputed timestamp and another that modifies the displayed output.

(In all the examples below, I'll be using UTC as my local timezone. You can setup this with SET TIMEZONE TO 'UTC')

To modify the input

You can use at time zone to tell Postgres that the time you are entering should be interpreted with another timezone. 

select timestamp '2000-01-01 12:00:00' at time zone 'utc+5';

Which gives a 5 hour difference in the displayed time:

        timezone        
------------------------
 2000-01-01 17:00:00+00

This is equivalent to giving the timezone in the inputed timestamp itself (look at the '-5' at the end of the timestamp):

select timestamp with time zone '2000-01-01 12:00:00-5';

Using at time zone in this way, you can tell Postgres that you are entering data in another timezone than the one currently configured, and Postgres will convert the output accordingly to match your local timezone (in our case, UTC).

To modify the output

In the examples above, the outputted timezone was assumed to be your local timezone, but you can modify this using at time zone, but only if your timestamp already contains the time zone information. For example:

select timestamp with time zone '2000-01-01 12:00:00-5' at time zone 'utc+5';

Which, instead of displaying the output as our local timezone, preserves the original one:

      timezone       
---------------------
 2000-01-01 12:00:00

Which is equivalent to:

select timestamp '2000-01-01 12:00:00' at time zone 'utc+5' at time zone 'utc+5';

The first at time zone defines the input time zone of your timestamp, while the second one defines the displayed timestamp in the output.

PS: I wrote all this in an effort to wrap my head around timezones in Postgres and I know that the documentation explains this conceptually in another way, but this way seemed more clear. Any clarifications are appreciated.

Upvotes: 1

Related Questions