public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Bruce Momjian <[email protected]>
Cc: PostgreSQL-documentation <[email protected]>
Subject: Re: AT TIME ZONE correction
Date: Sun, 02 Sep 2018 14:21:58 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

Bruce Momjian <[email protected]> writes:
> !     The <literal>AT TIME ZONE</literal> construct allows the addition,
> !     conversion, and removal of time zones for time stamp values.  <xref
>       linkend="functions-datetime-zoneconvert-table"/> shows its
>       variants.

Maybe it'd be more to the point to say that it allows conversion between
"timestamp with time zone" and "timestamp without time zone".

> !     The first example takes a time stamp without time zone and interprets
> !     it in the MST time zone (UTC-7), returning a time stamp with time
> !     zone value which is displayed in local time (PST, UTC-8).  The second
> !     example takes a time stamp with time zone value (EST, UTC-5) and
> !     converts it to the date and time in MST (UTC-7) without time zone.
> !     Basically, the first example takes the date and time and puts it in
> !     the specified time zone.  The second example takes a time stamp with
> !     time zone and shifts it to the specified time zone. (No time zone
> !     designation is returned.)

I still find this to be more confusing than helpful.  In particular,
I do not think that it's possible to explain this behavior clearly
without mentioning that timestamp with time zone values are always
stored in UTC and what AT TIME ZONE really does is convert between UTC
and the specified zone (in a direction dependent on which type is
supplied as argument).

			regards, tom lane

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: AT TIME ZONE correction
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox