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

[Anton Shishkov]

On Mar 31, 11:29 am, Michael Ricordeau <michael.ricord... at gmail.com>
wrote:
> 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... at semanchuk.com> a écrit :
>
>
>
>
>
> > On Mar 30, 2010, at 4:47 PM, ASh 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"))
>
> > > ...
> > > psycopg2.DataError: invalid input syntax for type timestamp with time
> > > zone: "NOW() - '29 days'::INTERVAL"
>
> > 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

thank you for good examples (bow)