Re: Oddity with time zones.

Steve Rogerson <steve.pg@yewtc.demon.co.uk> writes:
> # select (now());
>               now
> -------------------------------
>  2017-04-03 11:57:09.891043+01
> (1 row)

> sjr_local1db=# select (now() AT TIME ZONE  'UTC');
>           timezone
> ----------------------------
>  2017-04-03 10:57:11.714571
> (1 row)

> sjr_local1db=# select (now() AT TIME ZONE  'UTC') AT TIME ZONE 'UTC';
>            timezone
> -------------------------------
>  2017-04-03 11:57:14.088515+01
> (1 row)

> This makes no sense to me.

Looks perfectly fine from here.  You're rotating a timestamp with time
zone (displayed in your local zone, evidently GMT+1) to a timestamp
without time zone expressed in UTC, and then back to a timestamp with time
zone.  That round trip should be a no-op, barring weird corner cases.

I'd be the first to agree that the notation is pretty opaque --- why
use the same "operator" for both transformation directions? --- but
don't blame us, blame the SQL spec.

            regards, tom lane