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@xxxxxxxxxxxxx> wrote: > "Randy Shelley" <randy.shelley@xxxxxxxxx> 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@xxxxxxxxxxxxxxxx, http://ScienceTools.com/
