Invalid input syntax for type interval in postgresql

Question

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.

I removed the inconsistent database tags. Please tag only with the database you are really using. — Gordon Linoff
– Gordon Linoff, Commented Nov 6, 2020 at 1:29

GMB · Accepted Answer · 2020-11-06 01:40:53Z

4

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

answered Nov 6, 2020 at 1:40

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Invalid input syntax for type interval in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related