CODEWITHSUNDEEP
mysqldate
John Thomas
John Thomas

Reputation: 1115

How to filter based on date in certain cases in SQL?

So let's say I have data like this:

 ID   GROUP               TIMESTAMP1    col_OTHER               TIMESTAMP2
001      AA  2021-04-02 15:02:33.319       mangos
002      BB                                 kiwis
004      AA  2021-04-02 03:51:35.279      oranges
003      DD  2021-04-03 18:24:23.469      oranges  2021-04-03 18:22:23.469 
003      DD                               bananas  2021-04-03 15:02:33.319
002      CC  2021-04-04 11:02:51.313       apples  2021-04-04 11:03:51.313

So I want to filter just the rows where TIMESTAMP2 is after TIMESTAMP1 . We only started collecting data on TIMESTAMP2 April 3, no nulls after this but we have no data on this prior. So I want to apply this condition after this date.

Additionally we sometimes have null values in TIMESTAMP1, I think my coalesce() solves that...?

Final output:

 ID   GROUP               TIMESTAMP1    col_OTHER               TIMESTAMP2
001      AA  2021-04-02 15:02:33.319       mangos
002      BB                                 kiwis
004      AA  2021-04-02 03:51:35.279      oranges 
003      DD                               bananas  2021-04-03 15:02:33.319
002      CC  2021-04-04 11:02:51.313       apples  2021-04-04 11:03:51.313

And here's what I have thus far:

SELECT * 
FROM dt
WHERE coalesce(TIMESTAMP1, '1970-01-01') < TIMESTAMP2

I would also be curious how to remove instances where I have NULL in both TIMESTAMP1 and TIMESTAMP2

Upvotes: 1

Views: 37

Answers (2)

CaveCoder
CaveCoder

Reputation: 791

What about something like this?

 SELECT * 
    FROM dt
    WHERE TIMESTAMP1 is not null and TIMESTAMP2 is not null and TIMESTAMP1 < TIMESTAMP2

Upvotes: 0

forpas
forpas

Reputation: 164224

I think that this should work:

SELECT * 
FROM dt
WHERE COALESCE(TIMESTAMP1, '1970-01-01') < '2021-04-03'
   OR COALESCE(TIMESTAMP1, '1970-01-01') < TIMESTAMP2

or:

SELECT * 
FROM dt
WHERE COALESCE(TIMESTAMP1, '1970-01-01') < COALESCE(TIMESTAMP2, '2021-04-03')

See the demo.
Results:

ID GROUP TIMESTAMP1 col_OTHER TIMESTAMP2
001 AA 2021-04-02 15:02:33.319 mangos null
002 BB null kiwis null
004 AA 2021-04-02 03:51:35.279 oranges null
003 DD null bananas 2021-04-03 15:02:33.319
002 CC 2021-04-04 11:02:51.313 apples 2021-04-04 11:03:51.313

Upvotes: 2

Related Questions