Re: PGparam timestamp question - Mailing list pgsql-hackers

Andrew Chernow <ac@esilo.com> writes:
> Okay, thanks.  So using WITHOUT TIME ZONE basically means, store the provided
> value as UTC.  Meaning, 8AM EST NOW() is stored/treated as 8AM UTC.

No, I think you are more confused now than you were before.

For both types, the underlying stored value is just a number-of-seconds
offset from 2000-01-01 00:00:00.  The question is what's the reference
time really.  For WITHOUT TIME ZONE, what you see is what you get: it's
just a date and time, and nobody is actually promising anything about
timezone considerations.  For WITH TIME ZONE, the convention is that the
reference time is 2000-01-01 00:00:00 UTC, and therefore it is accurate
to say that the *stored value* is always expressed in UTC.

What confuses people is that for display purposes, a TIMESTAMP WITH TIME
ZONE value is rotated to your local timezone (as set by the timezone
variable) and printed with your local zone offset.  But that's not
what's really stored.

Now, about AT TIME ZONE: that's a trickier operation than it looks.
When you start with a timestamp WITH time zone, the meaning is
"here's a UTC time, give me the equivalent local time in this time
zone".  What comes out is a timestamp WITHOUT time zone, which means
it'll just be printed as-is.
When you start with a timestamp WITHOUT time zone, the meaning is
"here is a local time in this time zone, give me the equivalent
UTC time".  What comes out is a timestamp WITH time zone, which as
we already saw is implicitly UTC inside the system, which is correct.
But you have to remember that that value will be rotated back to
your local zone for display.  I think that extra conversion is what
was confusing you to start with.

Another point to keep in mind is that if the system is forced to
assume something about the timezone of a WITHOUT TIME ZONE value,
it will assume your local time zone setting.  In particular this
happens during forced coercions between WITH and WITHOUT TIME ZONE.
So for example, in

regression=# select now(), now()::timestamp without time zone;             now              |            now

 
-------------------------------+----------------------------2007-12-09 13:21:50.619644-05 | 2007-12-09 13:21:50.619644
(1 row)

the two values are in fact different numbers-of-seconds internally.
They print the same, but that's because in the first case the
timestamp-with-time-zone output routine rotated from UTC to my
local zone (EST) during printout.  In the second case the same
5-hour offset was applied by the cast to without-time-zone, and
then the timestamp-without-time-zone output routine just printed
what it had without any magic.
        regards, tom lane