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

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.


  Sam