CODEWITHSUNDEEP
sqlpostgresql
HellaF10
HellaF10

Reputation: 31

Daterange till with null date

I have this example

FROM           TILL
2016-02-27  2016-12-31
2017-01-01    <null>

I need the dates between 2013 and 2017, but when the TILL-Date is null this query doesn´t work. I just get the first row, not the second. Tryed it with from > COALESCE('2017',null) but this doenst work.

where not (FROM > '2017-12-31' or TILL < '2013-01-01' )

edit Tim

FROM           TILL
2017-06-16  2018-05-31
2018-06-01  <null>

Upvotes: 0

Views: 931

Answers (3)

user330315
user330315

Reputation:

When using Postgres you have a better way of dealing with that - you can create a date range that can easily deal with a NULL value in the upper bound:

select *
from the_table
where daterange(from_date, till_date, '[]') && daterange(date '2013-01-01', date '2018-01-01', '[)')

The && is the "overlaps" operator - you can't really use the "contains" operator (@>) because your row with the null value for till means "from 2017-01-01 to eternity" and a closed range like from 2013 to 2017 does not "include" a range that runs until "eternity"

Online example: https://rextester.com/KBMK53890

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522084

You may try the following logic:

SELECT *
FROM yourTable
WHERE
    "FROM" <= '2013-01-01' AND
    (TILL > '2018-12-31' OR TILL IS NULL);

This would include all records whose FROM to TILL range spans the years 2013-2018, inclusive. I am assuming that you are storing dates only, not timestamps. Otherwise, you would need to compare TILL against 2018-12-31 23:59:59.

Please avoid naming your columns and other database objects using reserved keywords such as FROM. Otherwise, you will have to escape those name in all your code.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

try like below add null checking for till in where condition

where (FROM>='2013-01-01' and till<='2018-12-31')
or (FROM>='2013-01-01' and till is null)

another way by using coalesce()

 where FROM>='2013-01-01' and coalesce(till,'2018-12-31')<='2018-12-31')

Upvotes: 0

Related Questions