[SOLVED] Invalid input syntax for type interval in postgresql

Invalid input syntax for type interval in postgresql

I have a query like the following

CAST(kokyaku1Information2.mail_jyushin as integer) as information2_mail_jyushin,
(date '$mytime' - INTERVAL 'information2_mail_jyushin' day) AS modified_date,

When run the query i get an error like invalid input syntax for type interval. I used another select field named information2_mail_jyushin before day.

Solution

In Postgres, you would use interval arithmetics like this:

kyaku1Information2.mail_jyushin::int AS information2_mail_jyushin,
date '$mytime' 
    - kokyaku1Information2.mail_jyushin::int * interval '1 day' 
    AS modified_date

Note that concatenating variables in a SQL statement is bad practice, and opens up your code to SQL injection attacks. Instead, use parameters, as in:

$1::date 
    - kokyaku1Information2.mail_jyushin::int * interval '1 day' 
    AS modified_date