Kevin Jacobs wrote:
On Wed, 27 Feb 2002, M.-A. Lemburg wrote:
SQL databases don't deal with leap seconds. They store the broken down value (in some way) without time zone information and that's it, fortunately :-)
Er... SQL99 (and I believe SQL92) have native support for time with and without time zones, and neither say nothing about how databases are to "store" those values. I don't have a copy in front of me, so I can't tell you what they say about leap-seconds. Of course, few implementations support this yet, though it worth being forward-looking.
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:
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).
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).
-- Marc-Andre Lemburg CEO eGenix.com Software GmbH