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
