Re: Storage sizes for dates/times (documentation bug?)

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(at)comcast(dot)net