Re: TIMESTAMP WITHOUT TIME ZONE

On Wed, 13 Dec 2006, Richard Huxton wrote:
> 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.

WRONG. It's a point in time that's meaningful to ME even if YOU can't tell
where in the universe it's supposed to represent relative to any other
point.

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

OHMYGODYOUJUSTHAVETOBEWRONG!!!

Let me get this straight; You're saying that you SUPPORT the idea of
conversion being performed by the database (or it's cohort, the JDBC
library in this case) when I, the author of an application using the data,
depend on my database to give me back the data I gave it?!

HORRIBLY BROKEN IF SO.

> 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.

...I missed the start of this thread but the CORRECT behavior for Postgres
regarding TIMESTAMP WITHOUT TIMEZONE is to take a timestamp in whatever
form _I_ care to give it and return it _exactly,_ unmodified in any way.

See my post from a few minutes ago, but simply put, time/date is at least
as challenging as money or multibyte character. And, simply put, the
Postgres implementation of timezone is INSUFFICIENT.

PLEASE tell me that when I give a PostgreSql server, through JDBC, a
timestamp, stored in a TIMESTAMP WITHOUT TIMEZONE attribute, that it'll
always give me back the _same_exact_bits_ as what I gave it! Anything else
is horribly broken and is, to quote Tom Lane, "about as good a definition
of corrupted data as I can think of." - with appologies to Tom, of course.

Regards,
Richard

--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy(at)ScienceTools(dot)com, http://ScienceTools.com/