Re: PGparam timestamp question - Mailing list pgsql-hackers

got it.  stored vs. displyed was confusing me.

Andrew


Tom Lane wrote:
> 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
> 
>