Re: Storage sizes for dates/times (documentation bug?) - Mailing list pgsql-general

On Tuesday 15 April 2008 6:31 am, Sam Mason wrote:
> On Tue, Apr 15, 2008 at 02:46:14PM +0200, Karsten Hilbert wrote:
> > Of course, the actual time stored in the database in UTC is
> > quite correct - it was indeed 3pm in location B when it was
> > 7am in London. But we need to know the original local time
> > (and also be able to know UTC since we want to correlate
> > times).
>
> I was under the impression that "timestamp without time zone" does
> precisely this.  It would be nicer if the docs highlighted the
> differences, and reasons behind, the semantics between the two, instead
> of focusing so much on the syntax.  The "WITH TIME ZONE" variant is
> described nicely:
>
>    For timestamp with time zone, the internally stored value is always
>    in UTC (Universal Coordinated Time, traditionally known as Greenwich
>    Mean Time, GMT). An input value that has an explicit time zone
>    specified is converted to UTC using the appropriate offset for that
>    time zone. If no time zone is stated in the input string, then it is
>    assumed to be in the time zone indicated by the system's timezone
>    parameter, and is converted to UTC using the offset for the timezone
>    zone.
>
>    When a timestamp with time zone value is output, it is always
>    converted from UTC to the current timezone zone, and displayed as
>    local time in that zone. To see the time in another time zone, either
>    change timezone or use the AT TIME ZONE construct (see Section
>    9.9.3).
>
> But there doesn't seem to be any similar description of the "WITHOUT
> TIME ZONE" option.  It mentions:
>
>   the date/time fields in the input value [...] is not adjusted for time
>   zone.
>
> But that's about all I could find.  I think that the actual semantics
> should be described and maybe a paragraph should be written highlighting
> differences with an example.  I'd be happy to write this if people
> agree.
>
> My reasoning goes something like this:  The WITH and WITHOUT clauses
> seem to be the opposite of my naive understanding of their purpose.  I'd
> think that if you specify WITH TIME ZONE then it means that the timezone
> is important to me, and I want to deal with it myself.  Whereas, the
> WITHOUT TIME ZONE clause would suggest that the timezone isn't important
> to me, and anything the database can do to make the problem go away the
> better.  What the spec says, and PG does, is actually the opposite.  The
> fact that this confusion can occur (and seems to occur reasonably often
> based on previous posts to the mailing lists) suggests that the docs
> should highlight the differences more clearly.
>
> I'd also hazard a guess that we don't hear about it more because most
> people just work within a single time zone and hence don't even notice
> the difference between the two.

My only comment is on this assertion. Any location that has DST rules has two
time zones.  For instance I live in  US PST/PDT.  Without timezone support
doing date/time math across time zone boundaries is asking for problems.

>
>
>   Sam

--
Adrian Klaver
aklaver@comcast.net