Hello Mario,
> some time ago I was writing about a patch I had written, to allow
> pgdb handle datetime data... the remarks I received made me
> refine my patch...
I've looked into your patch today.
> in python 2.1 the module datetime has been introduced, which makes
> mx.DateTime superfluous. I'm switching to that module.
> well ... support for mx.DateTime, that is now missing. one would
> need to inform the library if he wants to use datetime or mx.DateTime...
> you cannot base the choice on the simple availability of any of the
two...
> I did not look for a solution to this problem.
I think mx.datetime should be supported, since datetime was only
introduced in Python 2.3, not 2.1. We could revert the current behavior
and use datetime if available, and mx.DateTime if not. Plus, as you
said, there could be an option to prefer one over the other if both are
available. For instance, if you use pgdb along with mx.odbc, then pgdb
should use mx.DateTime for better compatibility (e.g. if you want to
copy data from some other database to your PostgreSQL database).
Also, there should be an option to not convert to datetime types, but
use the strings as delivered by the database. 1) Backward compatibility,
2) PostgreSQL databases runnin in non-ISO datestyles, 3) some strange
dates (like dates BC) or timezones may raise errors in conversion, so
you can always fallback to strings in these cases.
For instance, the connect() function of the pgdb module could take an
additional parameter "datetypes" which could be set to None = "str",
"datetime" or "mx" = "mx.datetime". If datetime or mx datetypes are
requested, then connect() could execute a "show datestyle" and check if
the database is really running with ISO datestyle and raise an error
otherwise. How do you think about that?
> anyways: the datetime module offers the three types date, datetime,
> timedelta.
I would also offer the time type instead of lumping it together with
timedelta. It is a separate type in PostgreSQL and Python. Also,
PostfreSQL renders it differently from datetime (without the date), so
you need to be able to distinguish it (if you are not using a clever
conversion function that distinguishes dates from times by the different
separators '-' and ':').
> datetime.timedelta ... (granularity? microsecond too?)
Yes. Both PostgreSQL and Python support microseconds in timedeltas.
Some comments regarding your conversion code:
elif typ == TIMEDATE:
parts = [int(i) for i in value.split("-")]
value = DateDate(*parts)
This will give ValueErrors for years <0 (with "BC")
and for years >9999. Should be acceptable.
elif typ == TIMESTAMP:
f = value + ".000000"
import re
parts = re.split(r"[\. :-]", f)[:7]
parts[6] = (parts[6]+'000000')[:6]
parts = [int(i) for i in parts]
value = DateTime(*parts)
That looks a bit too adventurous and it will fail
for timestamps with timezone info.
I suggest making two conversion functions for to_date()
and to_time(). The to_date() function should consider AD/BC
suffixes, the to_time() function should consider time zones.
The to_timestamp conversion function could then do:
value = split(value, 2)
if len(value) > 2: value[0] += ' ' + value.pop() # AD/BC
value = datetime.combine(value[0], value[1])
elif typ == TIMEINTERVAL:
import re
value = re.sub('[a-z]', '', value)
parts = re.split(r"[ :-]+", value)
days, hours, minutes, seconds = [float(i) for i in parts]
value = TimeDelta(int(days), seconds+60*(minutes+60*hours))
Again, this looks too adventurous and does not treat
negative intervals and intervals with microseconds correctly.
Regexps should not be needed.
Instead of this:
if isinstance(x, DateTimeType):
x = str(x)
elif isinstance(x, DateType):
x = str(x)
elif isinstance(x, TimeDeltaType):
x = str(x)
You can simply write:
if isinstance(x, (DateTimeType, DateType, TimeDeltaType)):
x = str(x)
Maybe you can continue to refine the patch based on these comments?
Otherwise I can do it if when find some time...
-- Christoph
_______________________________________________
PyGreSQL mailing list
[email protected]
http://mailman.vex.net/mailman/listinfo/pygresql
