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

[Michael Ricordeau]

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 <philip 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
>