CODEWITHSUNDEEP
postgresql
PlayMa256
PlayMa256

Reputation: 6841

Select between with null intervals

I want to do something like

SELECT * FROM mytable WHERE created_at BETWEEN A AND B

And these variables can be null, if that is null I wish I could fallback to the lower and upper bound on the data, what I mean:

Name Created_at
First 01-01-2021
Second 02-03-2021
Second 31-12-2021

A would be 01-01-2021 and B 31-12-2021, or something that has the same meaning. How can I do that?

Upvotes: 2

Views: 1186

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246708

Use the power of coalesce:

SELECT * FROM mytable
WHERE created_at BETWEEN coalesce(
                            CAST (A AS timestamp with time zone),
                            '-infinity'
                         )
                     AND coalesce(
                            CAST (B AS timestamp with time zone),
                            'infinity'
                         );

Upvotes: 4

Related Questions