[Pypgsql-devel] [ pypgsql-Bugs-653044 ] DateTimeDelta does not work if >= 1 day

[<no...@so...>]

Bugs item #653044, was opened at 2002-12-13 01:24
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=653044&group_id=16528

Category: None
Group: None
Status: Open
Resolution: Fixed
Priority: 7
Submitted By: Ondrej Palkovsky (ondrap)
Assigned to: Billy G. Allie (ballie01)
Summary: DateTimeDelta does not work if >= 1 day

Initial Comment:
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'


----------------------------------------------------------------------

>Comment By: Ondrej Palkovsky (ondrap)
Date: 2002-12-19 22:11

Message:
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.

----------------------------------------------------------------------

Comment By: Billy G. Allie (ballie01)
Date: 2002-12-14 06:16

Message:
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.

----------------------------------------------------------------------

Comment By: Billy G. Allie (ballie01)
Date: 2002-12-13 15:27

Message:
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.

----------------------------------------------------------------------

Comment By: Ondrej Palkovsky (ondrap)
Date: 2002-12-13 10:03

Message:
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.

----------------------------------------------------------------------

Comment By: Billy G. Allie (ballie01)
Date: 2002-12-13 08:16

Message:
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.

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=116528&aid=653044&group_id=16528