CODEWITHSUNDEEP
sqlpostgresql
Heisenberg
Heisenberg

Reputation: 5279

invalid input syntax for type interval in Postgres

When I execute following query, it returned errors

SELECT 
    type1,
    type2
FROM 
    table
WHERE 
    date  >= '2021-05-22' - interval '22 day'

Invalid operation: invalid input syntax for type interval: "2021-05-22"

I'd like to set like '2021-05-22' in hard coding style. Where is the wrong point of this ?

If someone has any insight, please let me know.

Thanks

Upvotes: 0

Views: 2580

Answers (2)

Jim Jones
Jim Jones

Reputation: 19603

if you're dealing with days, you don't need to use interval. Just adding or subtracting an integer is enough:

SELECT current_date, current_date + 22, current_date - 22;

 current_date |  ?column?  |  ?column?  
--------------+------------+------------
 2021-05-27   | 2021-06-18 | 2021-05-05

So, this should work:

SELECT * FROM table
WHERE date  >= '2021-05-22'::date - 22;

Demo: db<>fiddle

Check this other answer

Upvotes: 2

Schwern
Schwern

Reputation: 164639

Postgres is strongly typed. Sometimes it will typecast for you, but it won't guess based on the content of the text. When you give it text - interval it is trying to cast the text into an interval, not a date.

You need to cast the text to a date explicitly.

select '2020-05-22'::date - interval '22 day';

You could also simplify this.

select '2020-04-30'::date

Upvotes: 3

Related Questions