From: Brian Candler <B.Candler@...>
Date: 2003-05-30T07:53:02+09:00
Subject: Re: Metakit for Ruby - Would you want it?

On Fri, May 30, 2003 at 06:44:28AM +0900, ahoward wrote:
> > It's awful. Even worse is date and time handling.
> 
> i fell your pain - and my application is nothing but data and time handling
> which is why i'm so anti dbi.  in my mind dbi (or similar) give you as many
> headaches with none of the perks (since you can't take advantage of db
> specific feature w/o losing portability) - sort of like writing OO code in c++
> ;-) genericity is, all too often, evil.

To be fair, it's often the databases themselves which are broken. I was
shocked when I discovered that Oracle's DATE type (which actually stores a
date+time to the second) stores a YYYYMMDDhhmmss value without any concept
of a timezone. So if someone writes an app which does

     insert into foo (timestamp) values (sysdate);

then what you get is a *local time* value stored. For anything other than
mickey-mouse applications this is pretty useless.

The DBI layer in principle could at least abstract a BCD timestamp
YYYYMMDDhhmmss[.sss] into a consistent form, and leave it up to the
application to deal with the problems of timezones. But I haven't seen this
done properly. In particular, when you insert into a date column, you
generally end up having to format dates to match what the database expects.
That, I think, is the fault of the SQL spec, or lack of it; ultimately the
SQL query has to be an ASCII string to send to the database.

So, if you're writing an application from scratch, IMO timestamps are much
better just stored as INTEGER values containing Unix UTC values.
Unfortunately, you often have to interface with an existing broken database
design :-(

Regards,

Brian.