Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

psycopg2 / psycopg2.DataError: invalid input syntax for type timestamp with time zone:

624 views
Skip to first unread message

ASh

unread,
Mar 30, 2010, 4:47:42 PM3/30/10
to
Hi, please help me understand why am I getting error with this query


new_start_date = "NOW() - '29 days'::INTERVAL"
self.dyndb.orderdb.query('''update xxxx set creation_date
= %s
where id_order = %s''', (new_start_date, "123"))

...
psycopg2.DataError: invalid input syntax for type timestamp with time
zone: "NOW() - '29 days'::INTERVAL"


Philip Semanchuk

unread,
Mar 30, 2010, 5:26:51 PM3/30/10
to python-list (General)

Hi Anton,
It sounds to me like the problem is with your SQL rather than with
psycopg2 or Python. Try the query directly in Postgres -- does it work
there? If so, then your next step should be to ask on the psycopg2
mailing list that Google can find for you.

Good luck
Philip

D'Arcy J.M. Cain

unread,
Mar 30, 2010, 5:50:56 PM3/30/10
to ASh, pytho...@python.org
On Tue, 30 Mar 2010 13:47:42 -0700 (PDT)
ASh <anton.s...@gmail.com> wrote:
> Hi, please help me understand why am I getting error with this query
>
>
> new_start_date = "NOW() - '29 days'::INTERVAL"
> self.dyndb.orderdb.query('''update xxxx set creation_date
> = %s
> where id_order = %s''', (new_start_date, "123"))

Put single quotes around the first %s in the query.

--
D'Arcy J.M. Cain <da...@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

ASh

unread,
Mar 30, 2010, 6:45:03 PM3/30/10
to

sql itself is correct

ASh

unread,
Mar 30, 2010, 6:46:12 PM3/30/10
to
On Mar 31, 12:50 am, "D'Arcy J.M. Cain" <da...@druid.net> wrote:
> On Tue, 30 Mar 2010 13:47:42 -0700 (PDT)
>
> ASh <anton.shish...@gmail.com> wrote:
> > Hi, please help me understand why am I getting error with this query
>
> >             new_start_date = "NOW() - '29 days'::INTERVAL"
> >             self.dyndb.orderdb.query('''update xxxx set creation_date
> > = %s
> >             where id_order = %s''', (new_start_date, "123"))
>
> Put single quotes around the first %s in the query.
>
> --
> D'Arcy J.M. Cain <da...@druid.net>         |  Democracy is three wolveshttp://www.druid.net/darcy/               |  and a sheep voting on

> +1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Tried like you said, got this error:

psycopg2.ProgrammingError: syntax error at or near "NOW"
LINE 1: update orderdb.orders set creation_date = 'E'NOW() - ''29
da...
^

Steve Holden

unread,
Mar 30, 2010, 7:44:53 PM3/30/10
to pytho...@python.org
D'Arcy J.M. Cain wrote:
> On Tue, 30 Mar 2010 13:47:42 -0700 (PDT)
> ASh <anton.s...@gmail.com> wrote:
>> Hi, please help me understand why am I getting error with this query
>>
>>
>> new_start_date = "NOW() - '29 days'::INTERVAL"
>> self.dyndb.orderdb.query('''update xxxx set creation_date
>> = %s
>> where id_order = %s''', (new_start_date, "123"))
>
> Put single quotes around the first %s in the query.
>
And in future please tell us exactly what error you are trying to
explain by quoting the traceback exactly.

regards
Steve
--
Steve Holden +1 571 484 6266 +1 800 494 3119
See PyCon Talks from Atlanta 2010 http://pycon.blip.tv/
Holden Web LLC http://www.holdenweb.com/
UPCOMING EVENTS: http://holdenweb.eventbrite.com/

D'Arcy J.M. Cain

unread,
Mar 30, 2010, 8:10:34 PM3/30/10
to ASh, pytho...@python.org
On Tue, 30 Mar 2010 15:46:12 -0700 (PDT)
ASh <anton.s...@gmail.com> wrote:
> > >             new_start_date = "NOW() - '29 days'::INTERVAL"
> > >             self.dyndb.orderdb.query('''update xxxx set creation_date
> > > = %s
> > >             where id_order = %s''', (new_start_date, "123"))
> >
> > Put single quotes around the first %s in the query.
>
> Tried like you said, got this error:
>
> psycopg2.ProgrammingError: syntax error at or near "NOW"
> LINE 1: update orderdb.orders set creation_date = 'E'NOW() - ''29
> da...

Right. I misread it. Please show us the exact error that you get with
the original code.

Message has been deleted

Michael Ricordeau

unread,
Mar 31, 2010, 4:29:43 AM3/31/10
to pytho...@python.org
Hi

You cannot add 'NOW() - '29 days'::INTERVAL' as a query because cursor.execute() will try to mogrify it.

You can do :
import datetime
idays = psycopg2.extensions.adapt(datetime.timedelta(days=29))
self.dyndb.orderdb.query('update xxxx set creation_date=(NOW() - %s) where id_order=%s', idays, "123"))

Or:
import datetime
interval = datetime.datetime.now() - datetime.timedelta(days=29)
self.dyndb.orderdb.query('update xxxx set creation_date=%s where id_order=%s', (interval, "123"))
# But in this case current date/time is not evaluated from postgresql server but only from python env ... this may cause some bugs

You may also try to add an interval type with psycopg2.extensions.INTERVAL (I never played with it)

Le Tue, 30 Mar 2010 17:26:51 -0400,
Philip Semanchuk <phi...@semanchuk.com> a écrit :

>
> On Mar 30, 2010, at 4:47 PM, ASh wrote:
>

Anton Shishkov

unread,
Mar 31, 2010, 4:42:41 AM3/31/10
to
On Mar 31, 3:10 am, "D'Arcy J.M. Cain" <da...@druid.net> wrote:
> On Tue, 30 Mar 2010 15:46:12 -0700 (PDT)
>
> ASh <anton.shish...@gmail.com> wrote:
> > > >             new_start_date = "NOW() - '29 days'::INTERVAL"
> > > >             self.dyndb.orderdb.query('''update xxxx set creation_date
> > > > = %s
> > > >             where id_order = %s''', (new_start_date, "123"))
>
> > > Put single quotes around the first %s in the query.
>
> > Tried like you said, got this error:
>
> > psycopg2.ProgrammingError: syntax error at or near "NOW"
> > LINE 1: update orderdb.orders set creation_date = 'E'NOW() - ''29
> > da...
>
> Right.  I misread it.  Please show us the exact error that you get with
> the original code.
>
> --
> D'Arcy J.M. Cain <da...@druid.net>         |  Democracy is three wolveshttp://www.druid.net/darcy/               |  and a sheep voting on

> +1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Error:
cursor.execute(sql, params)
File "/opt/local/lib/python2.5/site-packages/psycopg2/extras.py",
line 118, in execute
return _cursor.execute(self, query, vars, async)


psycopg2.ProgrammingError: syntax error at or near "NOW"
LINE 1: update orderdb.orders set creation_date = 'E'NOW() - ''29
da...

^

Anton Shishkov

unread,
Mar 31, 2010, 6:50:39 AM3/31/10
to
On Mar 31, 11:29 am, Michael Ricordeau <michael.ricord...@gmail.com>
wrote:

thank you for good examples (bow)

0 new messages