On Wed, 27 Feb 2002, M.-A. Lemburg wrote:
True, SQL-92 defines data types "TIME WITH TIME ZONE" and "TIMESTAMP WITH TIME ZONE". The standard is only available as book, but here's a draft which has all the details:
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Still, only Oracle and PostgreSQL seem to actually implement these and ODBC (SQL/CLI), the defacto standard for database interfacing, doesn't even provide interfaces to query or store time zone information (you can put the information directly in the SQL string, but not use it in bound variables).
Strangely enough I use TIMESPAMP WITH TIMEZONE quite a bit on both Oracle and PostgreSQL using native drivers. I'm also fairly sure that Sybase and MS-SQL store timestamps with timezone somehow, though my memory on the project that did so is a little fuzzy.
Basically, you should not store local time in databases, but instead use UTC. If you need the original time zone information for reference, you'd keep this in separate DB columns (e.g. as strings).
Why not minute offset from UTC like C99? Anyhow, everyone knows that time zones and daylight savings time are a pain to deal with. However, lets provide work toward a sane implementation that can relieve the end-user from having to smack their head against this particular brick wall every time. (even if it means smacking our collective heads against the brick wall until we're happy, or reduced to unintelligible ranting, or possibly both). Regards, -Kevin -- Kevin Jacobs The OPAL Group - Enterprise Systems Architect Voice: (216) 986-0710 x 19 E-mail: jacobs@theopalgroup.com Fax: (216) 986-0714 WWW: http://www.theopalgroup.com