Macbernie Macbernie - 1 year ago 290
PHP Question

PostgreSQL add X months to a date

I try to add X month to the current datetime:

UPDATE mytable SET thedate=NOW() + INTERVAL '12 month' WHERE id=1

But I got:

SQLSTATE[22007]: Invalid datetime format: 7 ERROR: invalid input syntax for timestamp: « NOW() + INTERVAL '12 month' »

What should be the correct syntaxe ?

Edit: seems it comes from my PHP prepare/execute syntax

$t = $connect->prepare('UPDATE mytable
SET thedate=:duree
WHERE id=:id');

$ok = $t->execute([
':duree' => "NOW() + INTERVAL '$duree month'",
':id' => $user,

Answer Source

The problem here is that you are trying to bind a variable with a SQL expression, which is not possible. You can only bind literal values. If you'd replace the binds out of what you showed, supposing $duree = 12, you'd get some SQL like this:

UPDATE mytable 
    SET thedate = 'NOW() + INTERVAL ''12 month'''
    WHERE id=:id

Which is clearly not correct, as 'NOW() + INTERVAL ''12 month''' is not a valid timestamp[tz], hence the error you get now.

As only the number of months is a parameter to you, you could simple multiple it as an integer with interval '1 month':

$t = $connect->prepare('UPDATE mytable 
    SET thedate = now() + (:duree  * interval '1 month')
    WHERE id=:id');

$ok = $t->execute([
    ':duree' => $duree,
    ':id'    => $user,
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download