[PYTHON DB-SIG] DbiDate in DBAPI
Thu, 20 Feb 1997 14:53:22 +0100
The DBAPI DbiDate definition have caused us a couple of problems.
0) There seem to be different definitions of the meaning of type DATE
between Oracle and Informix. Possibly also for others.
Oracle supplies DATE with year-month-day-hour-minute-second.
Informix supplies DATE as days since 18991231 and
DATETIME with year-month-day-hour-minute-second-fraction.
and allows for any contigous subset of that.
Oracle DATE and Informix DATETIME YEAR TO SECOND is the ANSI standard.
Informix also have INTERVAL (also ANSI).
1) There is no distinction between DATE and DATETIME.
Yes, this is an Informix thing but we use Informix, we use Oracle,
we are likely to start using others and we need to write generic code.
If other databases have their peculiarities, maybe we can define some
general method to make the distinctions.
3) There is no way to indicate the column format for a DATETIME.
Assume that I wish to INSERT a record with a DATETIME and I have
a dbiDate object with some time info. I then have two options:
1) Form a string representation of a complete INSERT statement
and execute. The date info must then be extracted from the
dbiDate object and transformed into suitable syntax for the
column at hand. If there is a change in the database schema,
I have to find all places in my code to make a change.
2) Tweak the implementation to retrieve the column definition
from database system tables and form a correct insert value (and
possibly coercing the time info to fit).
We have encountered cases where the database is defined by the customer
and we have been forced to write quite ugly code to work around this
lack of information and control.
Also, if I wish to write some generic code handling DATETIMES, I'm
stuck since all I have is time since unix epoch and can't make any
distinction between the case YEAR TO DAY and the case YEAR TO SECOND and
the time happens to be exactly midnight a certain day. In the former case,
there is little meaning to allow code to add 3 hours and 34 minutes which is
quite valid in the latter.
3) There is no way to denote a fraction of a second.
Ok, one could live without it but its defined by ANSI (for INTERVAL) and
there will surely pop up needs for it so why not try to support it.
Yes, I understand that much of this was considered as part of the basis for the
definition as it is. I just feel the definition inadequate and would very
much like to see it improved instead of just supplying extensions for
the Informix module (contradicting DBAPI def) or worse, supply another
module with its own api. (We have one.)
So that's my observations. How may we improve ?
I can see two ways:
1) Extend dbiDate to take constructor arguments defining type (DATE vs
DATETIME) and span (YEAR TO SECOND et al). For symmetry we need a
type attribute and a span attribute to allow us to see how the
column is defined.
On top of this we may create wrappers to our liking.
2) Define a new class with fullblown utilities. (Hmm, I remember some
discussion on a datetime module but that didn't deal with database
needs or ...)
Extending dbiDate as indicated should be simple enough.
Defining a new class may lead us to eternal discussions before we settle
what we want in it.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Bertil Reinhammar IV DocEye AB (Combitech)
phn. +46 13 200606 Teknikringen 9
fax. +46 13 214897 S-58330 Linköping
DB-SIG - SIG on Tabular Databases in Python
send messages to: firstname.lastname@example.org
administrivia to: email@example.com