invalid input syntax for type interval in Postgres

Question

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

You tag your question with postgre but your title says mysql. Please give us consitent informations — Jens
– Jens, Commented May 27, 2021 at 5:10

Schwern · Accepted Answer · 2021-05-27 05:24:00Z

3

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

answered May 27, 2021 at 5:24

Schwern

167k28 gold badges225 silver badges370 bronze badges

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

1 Comment

Laurenz Albe Over a year ago

It is actually unknown, not text, but your reasoning is right.

Jim Jones · Accepted Answer · 2021-05-27 05:37:29Z

2

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

edited May 27, 2021 at 5:37

answered May 27, 2021 at 5:27

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Collectives™ on Stack Overflow

invalid input syntax for type interval in Postgres

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related