Re: PGparam timestamp question

got it. stored vs. displyed was confusing me.

Andrew

Tom Lane wrote:
> Andrew Chernow <ac(at)esilo(dot)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
>
>