Re: TIMESTAMP WITHOUT TIME ZONE - Mailing list pgsql-general

Hi Tom, Randy, et al,

I'm not fully caught up with my Readings In Postgres, but this post caught
my eye and raised a concern...

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Randy Shelley" <randy.shelley@gmail.com> writes:
> > The java.sql.Timestamp does not store any timezone info, just nano seconds
> > from a date.
>
> One would hope that it's implicitly referenced to GMT, though, not some
> free-floating value that means who-knows-what.
>
> I think your fundamental error is in using timestamp without time zone
> in the database.  Try with-time-zone if you want consistent results
> across clients in different zones.
>

I sure hope there's no issue with using timestamp without timezone
_anywhere_ in the PG world because, quite frankly, "timezone" just doesn't
cut it.

There are so many issues that I don't think I have time to justice
to them here in a short email, but, just so we've all got some idea:
First, you need at least minute, if not second offset from GMT to have
anything like a comprehensive shot at "timezone." Hour-based time zones
are simply insufficient. There are lots of places in the world with
non-hour offsets from GMT.

The second biggest issue is probably the plethora of "daylight savings
time" schemes - and they change over time: notably within the last year, a
U.S. community muffed handling such a chnage with their Canadian
neighbors. And there are the timings of changes, too - do automated
daemons know when the time changes? It's quite different in various parts
of the world. Do you blindly follow your system clock? LOTS of questions
here that are none of PG's business, but are vital to a production system
always getting it right.

Third, any presumption about when which version of a time should be valid
is bound to cause major errors at some point or another. One can't just
always hand the user a timestamp in local time on client ends because you
don't know what kind of local processing they might wish to do outside of
the database engine, not the least of which is the type of question, "was
it after their business hours?" - a local-to-local question! Therefore, as
a minimum, you _must_ provide transform functions, one to the other, and
let the caller ask for what they wanted. This is particularly tricky when
it comes to database join statements - did you give the query the GMT
version, or local version?! -oy- The headaches this can cause, even among
experts.

We at Science Tools use "timestamp without timezone" as the basis of our
handling our customer's data correctly. It's configurable, but by default
all data going into a database is converted to GMT by our software,
outside the database engine, unless explicitly directed otherwise. To
handle the optionality of this, all join operations happen using what we
call "database time", so if a db doesn't store in GMT for some reason, we
still know what to do (for example, converting to the equivalent local
time of the server). We track client's GMT offsets - stored in the db, of
course - so we've got every client's offset data when needed, etc, etc,
etc.

...I PRESUME there's nothing broken about "timestamp without timezone"
within either the engine or the JDBC drivers, but I'd also caution to
always punt on the question of whether or not someone should or shouldn't
use Postgres' time zone feature. Perhaps a "for most people" qualifier,
or, "when every user is in an hour-offset from GMT timezone", etc., but
even then, multi-time-zone applications need to be VERY warry.

Respectfully,
Richard

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