CODEWITHSUNDEEP
sqlpresto
Slava
Slava

Reputation: 425

How to find entry that is between two dates?

I have a table as:

Id start_timestamp end_timestamp
1 2021-07-12 03:00:00   2021-07-13 11:58:05 
2 2021-07-13 04:00:00 2021-07-13 05:00:00
3 2021-07-13 04:00:00 2021-07-13 09:00:00
4 2021-07-13 04:00:00 NULL
5 2020-04-10 04:00:00 2020-04-10 04:01:00

....

I want to find all records that fall between two specific timestamps? Basically I'm looking to understand what process ran during a high pick time of the day (it doesn't matter if they have 1 sec in the window or hours.. just occurrence in the window is enough)

So if the timestamps are 2021-07-13 00:00:00 to 2021-07-13 04:30:00 The query will return

1
2
3
4

How can I do that with SQL? (Preferably Presto)

Upvotes: 0

Views: 27

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

This is the overlapping range problem. You may use:

SELECT *
FROM yourTable
WHERE
    (end_timestamp > '2021-07-13 00:00:00' OR end_timestamp IS NULL) AND
    (start_timestamp < '2021-07-13 04:30:00' OR start_timestamp IS NULL);

My answer assumes that a missing start/end timestamp value in the table logically means that this value should not be considered. This seems to be the logic you want here.

Upvotes: 1

Related Questions