Re: psycopg, datetime timestamps and intervals

Il giorno ven, 18/04/2008 alle 21.33 -0500, Matthew Dennis ha scritto:
> cur.execute("drop table if exists t0")
> cur.execute("create table t0(c0 timestamp(0) with time zone)")
> cur.execute("insert into t0 values(current_timestamp)")
> cur.execute("select c0 from t0 where c0 < %(bindArg)s - interval '1
> hour'", {'bindArg':datetime.utcnow()})
> 
> I get:
> File "./saerr2.py", line 12, in <module>
>     cur.execute("select c0 from t0 where c0 < %(bindArg)s - interval
> '1 hour'", {'bindArg':datetime.utcnow()})
> psycopg2.ProgrammingError: invalid input syntax for type interval:
> "2008-04-18T21:30:11.897334"

Actually, psql on PostgreSQL 8.2 gives exactly the same error:

test=> SELECT '2008-04-18T21:30:11.897334' - interval '1 hour';
ERROR:  invalid input syntax for type interval: "2008-04-18T21:30:11.897334"

psycopg (still) does not use explicit casts. You my want to add an
explicit cast to resolve such corner cases:

cur.execute("select c0 from t0 where c0 < %(bindArg)s::timestamp - interval '1 hour'",
	{'bindArg':datetime.utcnow()})

federico

-- 
Federico Di Gregorio                         http://people.initd.org/fog
Debian GNU/Linux Developer                                [email protected]
INIT.D Developer                                           fog-NGVKUo/i/[email protected]
 La mia nuova versione del mondo prevede una consapevolezza
  distribuita e insapore.                             -- sisterconfusion

_______________________________________________
Psycopg mailing list
Psycopg-IAPFreCvJWPBWskQ1e/[email protected]
http://lists.initd.org/mailman/listinfo/psycopg

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQBICbqNvcCgrgZGjesRAmkeAKCc64b6qF2Iy8qtaBwfcEHybVEV6gCfQa/d
SL2uf5oUAeSF6mTgCsPSKc4=
=YEGb
-----END PGP SIGNATURE-----