CODEWITHSUNDEEP
sqlpostgresqldatewhere-clausesql-in
Vishnukk
Vishnukk

Reputation: 564

Extract year complex query - PostgreSQL

I want to filter out my activities based on the multicombobox values with the last 4 Years. I am able to make a query to the database if I provide just one year.

SELECT * FROM public.events WHERE (EXTRACT(YEAR from start_date)) = 2019

However, I am not quite sure how can I give the query if I want to filter based on multiple years

SELECT * FROM public.events WHERE (EXTRACT(YEAR from start_date)) IN [2019, 2020]

is not working. How can I change my query?

Upvotes: 0

Views: 64

Answers (1)

GMB
GMB

Reputation: 222682

The expression you meant to write:

WHERE EXTRACT(YEAR from start_date) IN (2019, 2020)

That is, IN expects a list within parentheses, not square brackets.

But I would actually suggest using explicit range comparison instead:

where start_date >= '2019-01-01'::date and start_date < '2021-01-01'::date

The advantage of this approach is that it is SARGeable, meaning it can take advantage of an index on column start_date (while the original expression needs to extract() the year from each and every row before being able to actually filter).

Upvotes: 1

Related Questions