Re: now() AT TIME ZONE 'GMT';

Richard Emberson <emberson@phc.net> writes:
> If I want a column to have a default TIMESTAMP of 'now' but not in PST
> timezone but GMT,

It strikes me that you have a conceptual error.  Type TIMESTAMP (ie,
TIMESTAMP WITH TIME ZONE) *is* GMT internally; it is simply displayed
in whatever zone you've selected with SET TIMEZONE.  (This is basically
the same design as Unix system clocks --- always GMT --- and the TZ
environment variable.)  If you are trying to force it to a different
timezone then you are misusing it.

Type TIMESTAMP WITHOUT TIME ZONE doesn't have any concept of time zone
--- it's just a stated date and time with no particular zone reference.
If you apply the AT TIME ZONE operator to a TIMESTAMP WITH TIME ZONE
value, what happens is the internal GMT value is rotated to the
specified zone and then the output is labeled as type TIMESTAMP WITHOUT
TIME ZONE, preventing any further automatic zone rotations.  If you
coerce this back to TIMESTAMP WITH TIME ZONE, the implicitly assigned
zone is your local zone --- ie, your local zone is subtracted off again
to produce a supposed GMT value --- with entirely nonsensical results.

It wasn't clear to me exactly what you wanted to accomplish, but
applying AT TIME ZONE to something you are going to store in a TIMESTAMP
almost certainly isn't it.  My guess is that what you really want is
plain old unadorned "TIMESTAMP DEFAULT now()".

If you want to deliberately suppress time zone awareness, TIMESTAMP
WITHOUT TIME ZONE is the way to go.  If you want any awareness of zones,
you almost certainly want TIMESTAMP WITH TIME ZONE --- and just let the
system do what it wants to do, don't try to force some other approach.

            regards, tom lane