[DB-SIG] date/time handling

[Robert Brewer]

Mario Frasca wrote:
> ...the problem lies, as I see it, in the fact that the
> dbengine will return datetime values in different string
> formats depending on the setting of some datestyle within
> the engine, and that this format may, in principle,
> be changed even between different fetchone calls
> on the same query.

In principle, perhaps, but make the common case simple and the uncommon case possible. That is, don't penalize the 99% of consumers who will never see that datestyle change, ever. One way to accomplish that would be a module-level flag for datestyle for the common case, and a sync_datestyle() function that the 1% can call as often as they need to.

> well, in the solution I'm thinking about type information
> takes a crucial role.  data *coming* from the database
> keeps type information with itself, so that one can
> translate it in the desired way (a STRING "2003-05-12"
> is not the same as a DATE "2003-05-12"

Right. But converting to a Python type as early as possible saves a lot of headaches (not to mention code, memory and CPU cycles if you only do the conversion once).

> and the two things get potentially translated in a
> different way (I'm having problems on this point with the
> sqlite engine, which has no internal datetime types)).

Me too. :)

> but then also data *going* to the database must be
> clearly typed.

Yes. The value-adaptation layer must be aware of the target column types.

> you could think about a totally different approach,
> where data going into a specifically typed field
> gets translated in its own way, so a float going
> to a timestamp gets a different treatment than a
> float going to a number or to a date.
> ...
> I think that the strong dynamic typing in Python
> makes [that] undesirable (actually a nightmare I think).

Not really. In practice, there is a smallish set of Python types (int, long, str, unicode, float, date, datetime, timedelta, decimal, fixedpoint, list, tuple, dict, None, and bool), and a small set of database types which map *almost* one-to-one. In Dejavu [1], there are only 21 outbound adapters (that cover all 15 python types I listed), and only 15 inbound adapters; certain databases require a few of them to be overridden (e.g. for date formatting), but rarely are any new adapters added (usually for nonstandard database types, like CURRENCY for MS Access). So although it appears to be a nightmare M x N problem, in practice it's much closer to just M + N adapters.

Some people will have a need for Python types outside of the 15 I listed, or for database types nobody else has used. IMO it's proper for those people to have the burden of writing custom adapters for their own needs.

> I would also split the translation of data from the database
> to a datetime module in two parts.  one where the interface
> module produces a tuple of float or integer values using
> the data from the database and a second which translates
> these tuples into the correct datetime type. this second
> part would be caracteristic of the datetime module and
> could possibly be shared among the different interface modules.

YAGNI. I think at the end of that process, you'll find you've done a lot of separation of one-liners into two just for the sake of architectural beauty.


Robert Brewer
System Architect
Amor Ministries
fumanchu at amor.org

[1] http://projects.amor.org/dejavu/browser/trunk/storage/geniusql.py
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/db-sig/attachments/20060806/b10840b3/attachment.html