1

Say my table is this

validity table
 id          |     validity_dates      
-------------+-------------------------
          1 | [2018-01-01,2019-01-01)
          2 | [2017-05-01,2017-06-01)
          3 | [2016-05-01,2016-07-01)
          4 | [2022-01-01,2025-01-01)
          5 | [2022-01-01,2025-12-10)

How do I query to get the id based on whether the date range between validity_dates still exists within today and the future.

For example I want to get id's that are still valid from today and future. I run a query and get a return on id 4 and 5 since the validity dates ends in 2025.

My current implementation doesn't work in the sense it doesn't provide the right output.

SELECT id
FROM validity
WHERE validity.validity_dates = DATERANGE('2023-01-09','2025-01-09', '[]')

How best do I approach this?

Panface
  • 81
  • 6
  • try `where now() <@ validity_dates` – Mike Organek Feb 09 '23 at 16:30
  • more info on your validity table might be needed ? – Luuk Feb 09 '23 at 16:34
  • Hey @MikeOrganek, It's giving me an error ERROR: operator does not exist: timestamp with time zone <@ daterange LINE 3: WHERE now() <@ validity_dates ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. – Panface Feb 09 '23 at 16:43
  • @Luuk I've tried making it as simple as it could without showing too big of a table. What info would you need? – Panface Feb 09 '23 at 16:44
  • did you check this: https://stackoverflow.com/questions/10170544/getting-results-between-two-dates-in-postgresql ? – Luuk Feb 09 '23 at 17:05
  • I have checked that. It would make sense if the dates would have a start and an end. However for this table its conjoined into 1 column with a DATERANGE and 2 separate DATE. – Panface Feb 09 '23 at 17:20
  • 5
    1) This `'2025=01=09'` is invalid. 2) The error message tells you the problem `... timestamp with time zone <@`. So either `now()::date` or just do `where current_date <@ validity_dates`. – Adrian Klaver Feb 09 '23 at 17:21
  • It is unclear what data type you have in the `validitty_dates` column, (the column that holds the value `[2018-01-01,2019-01-01)`, that starts with a square bracket, and stops with a `)` – Luuk Feb 09 '23 at 17:36
  • 1
    @Luuk, it is a `daterange` from both the values shown and the error message `ERROR: operator does not exist: timestamp with time zone <@ daterange LINE 3: WHERE now() <@ validity_dates`. – Adrian Klaver Feb 09 '23 at 17:43
  • 1
    @AdrianKlaver: you are right, (strange datatypes do exists in PostgreSQL ), I created a [DBFIDDLE](https://dbfiddle.uk/UTRPQYSB) – Luuk Feb 09 '23 at 18:01
  • Hey @AdrianKlaver You've got the right answer for what I'm looking for. Also the query you mentioned was a typo, my bad. I've corrected it on my post. Thank you! Also Luuk thanks for making the DBFIDDLE link, much appreciated ! – Panface Feb 13 '23 at 10:25
  • Hey @AdrianKlaver I was wondering how you could date ranges in the future? For example, something happening in [2024-01-01, 2025-01-01]? – Panface Feb 20 '23 at 10:41
  • The same. You will have to be more specific about how you want to use future dates. Add as update to your question. – Adrian Klaver Feb 20 '23 at 16:03

0 Answers0