How to use between if value is not null?-CodePudding

Im trying to write query where im using valid_from and valid_to but those values can be null or one of them.

select * from omr_approval_teams where now() between valid_from and valid_to ;

This is my query, so i want to return data using if both valid_from and valid_to is null, or both not null or only valid_to is null. Any suggestion how can i achive that?

CodePudding user response：

You could just explicitly add passes for null date bounds:

SELECT *
FROM omr_approval_teams
WHERE (NOW() >= valid_from OR valid_from IS NULL) AND
      (NOW() <= valid_to   OR valid_to IS NULL);

CodePudding user response：

Alternatively you can use a timestamp range which automatically takes null as "unbounded":

If the columns are timestamp columns (without time zone)

SELECT *
FROM omr_approval_teams
WHERE tsrange(valid_from, valid_to, '()') @> localtimestamp;

If those two columns are timestamp with time zone you need to use:

SELECT *
FROM omr_approval_teams
WHERE tstzrange(valid_from, valid_to, '()') @> now();

The '()' parameter makes both edges inclusive. If you don't want that, adjust it accordingly.