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@xxxxxxxxxxx
