Postgresql-Check if today is outside Array of Dates

Question

I am beginner with Postgres. I want to select the rows where Today is greater than an Array of Dates that I have stored.

This is what i wrote which does not work (error)

    SELECT
    bool_or(now()::date @> a_date) AS some_of_the_dates_lies_within_range
FROM
(SELECT unnest(dateofdeparture) AS a_date from alerts) as S0;

This is how my row is

SELECT unnest(dateofdeparture) AS a_date from alerts

a_date
2019-01-12
2019-01-13
2019-01-14
2019-01-15
2019-01-16
2019-01-17
2019-01-18

Any pointers help would be much appreciated.

Kaushik Nayak · Accepted Answer · 2019-01-15 06:25:55Z

1

You are probably looking for the ANY or ALL operator on the ARRAY type

Assuming that dateofdeparture is a DATE[] array , what you need is either

select * from alerts WHERE current_date > ANY (dateofdeparture);

OR

select * from alerts WHERE current_date > ALL (dateofdeparture);

Demo

edited Jan 15, 2019 at 6:25

answered Jan 15, 2019 at 6:14

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

3 Comments

Laurenz Albe Over a year ago

Are you sure it is not > ALL that is asked for?

Sourav Chatterjee Over a year ago

That helped. So I am trying to filter all rows which are passed current date and this hopefully nailed it. select * from alerts WHERE NOT current_date < ANY (dateofdeparture);

Kaushik Nayak Over a year ago

@SouravChatterjee : So, ANY gave you the result you're expecting?

Collectives™ on Stack Overflow

Postgresql-Check if today is outside Array of Dates

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related