Re: TIMESTAMP WITHOUT TIME ZONE

Randy Shelley wrote:
> I get different result if I query it from my workstation(US/Easter
> timezone) and from the server (GMT timezone).

> A data type of timestamp without time zone should not do any
> conversions. The java.sql.Timestamp does not store any timezone info,
> just nano seconds from a date. Some where there is a timezone conversion
> happening. Why and how do I prevent it?

Tom's stated the problem, but to expand a little.

Your java.sql.Timestamp is an absolute point in time (presumably
measured from midnight 1970-01-01 GMT). Note that without the GMT there,
it would not be an absolute point in time since midnight in London was
different from midnight in New York.

The "timestamp without time zone" is NOT an absolute point in time, it
is only meaningful for a single time zone.

The "timestamp with time zone" IS an absolute time, but it DOES NOT
record the timezone you enter. Rather, it is equivalent to your
java.sql.Timestamp. If you have a client in London and another in New
York, both will display the same absolute time but in their local time
zone. So, I might see 14:00+00 whereas a New-Yorker might see 08:00+05
(if that's the right time-zone). You can ask for a specific time-zone
too (with AT TIME ZONE '...').

I think the biggest problem is that "with time zone" sounds like it's
storing a fixed time-zone when you insert a value.

--
Richard Huxton
Archonet Ltd