I think it is self-describing. It works for Delta up to
1 day though.
The following works:
c.execute("insert into test values (cast(%s as
interval))",(t.seconds,))
so I guess something like:
if isinstance(value,DateTime.DateTimeDeltaType):
return 'cast(%s as interval)' % value.seconds
in the quotation function would do it.
Problem reproduction:
ondra2=> create table test (a interval);
CREATE
ondra2=> \q
ondra@linux:~$ python
>>> from pyPgSQL import PgSQL
>>> a = PgSQL.connect('::ondra2')
>>> c = a.cursor()
>>> from mx import DateTime
>>> t = DateTime.Parser.TimeDeltaFromString('2 days')
>>> t
<DateTimeDelta object for '2:00:00:00.00' at 82aba40>
>>> c.execute("insert into test values (%s)",(t,))
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File
"/usr/lib/python2.2/site-packages/pyPgSQL/PgSQL.py",
line 2671, in execute
raise OperationalError, msg
libpq.OperationalError: ERROR: Bad interval external
representation '2:00:00:00.00'
Logged In: YES
user_id=8500
Ondrej,
The underlying problem is an incompatibility of the formats
used for intervals in PostgreSQL and DateTimeDeltas in mx.
In your example, the 2:00:00:00.00 would have to be cast as
'2 days 00:00:00.00' for PostgreSQL to accepts. There is
also a problem with intervals > 1 day begin returns from
PostgreSQL. The returned string can't be parsed by
mx.DateTime.DateTimeDeltaFromString.
I am working on a fix for the problem.
Logged In: YES
user_id=88611
BTW: There is a problem with DateTime type in Postgres 7.3
too. 7.2.3 works OK, but a friend reported that in 7.3 he
had to modify the mx.DateTime.ISO . I think you should test
it on this version too.
As for the 'interval', 7.2.3 can eat postgres output
correctly, I had only a problem with the conversion
python->postgres.
Logged In: YES
user_id=8500
Try this:
In psql insert '1 year 3 week 3 day ago'.
Retrieve the value from the table using psql.
Retrieve the value using pyPgSQL.
It's broken.
Also, can you or your friend submit a bug report about the
problem and the change to ISO needed to make it work?
Thanks.
BTW: I will be commiting a fix for the interval problem today.
Logged In: YES
user_id=8500
I have just commited a patch to PgSQL.py that will fix the
problem with theinterval parsing.
Ondrej,
Can you confirm that it corrects the poblem you were having.
Thanks.
Logged In: YES
user_id=88611
Apply the attached patch and it will be perfect !! (I have
just upgraded to 7.3 and IMO my friend does not have set the
date format to ISO. Isn't there some kind of 'SET DATEFORMAT
TO ISO' or something like that in Postgres to force this
format for a connection?) The patch fixes problems with
simple values like '2 hours', '2 days' etc. It seems to me
you tested it only on the complex ones.
Logged In: YES
user_id=88611
I forgot to atach the patch...
Logged In: YES
user_id=8500
It is now fixed in CVS.