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

If I want a column to have a default TIMESTAMP of 'now' but not in PST
timezone but GMT,
is the best way of doing it the following:

change_time TIMESTAMP DEFAULT now() AT TIME ZONE 'GMT' NOT NULL,

Richard

Richard Emberson <emberson(at)phc(dot)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

(on -hackers)

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

Hmm. That is how it probably *should* work, but at the moment the
timestamptz_zone() function actually outputs a character string! That is
a holdover from previous versions which did not have a "no zone"
timestamp; it would seem now to be more appropriate to output a no-zone
timestamp.

I'll look at changing this in my upcoming patch set...

- Thomas

Tom Lane wrote:

> Richard Emberson <emberson(at)phc(dot)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,

I guess so. So it is during the retrieval of a timestamp using jdbc, where
the timestamp
is converted into a java.sql.Timestamp that it gets converted to a local
timezone.
So I will have to use the jdbc access method that preserves (actually where
one can
set the desired timezone) the timezone.

Richard