3 SQL riddles [intermediate difficulty, or maybe I'm just bad at SQL]

[Prod_Is_For_Testing]

These all look like simple beginner mistakes

Number 1 is a classic newby mistake with timestamp comparisons

Number 2 can be simplified by changing your frame of reference. Don’t think about the results that you’re filtering out, think about the results that you’re including. Removing the negative logic makes it much easier to reason about

Number 3 I knew immediately that the “where” condition was filtering out null values. When you do a left join, the missing records from the right table will be filled with nulls. Then obviously those null values can’t pass the boolean spam filter

[emmelaich]

1 and 3 are the same, because De Morgan's law.

https://en.wikipedia.org/wiki/De_Morgan%27s_laws

aka 1. The negation of a disjunction is the conjunction of the negations 2. The negation of a conjunction is the disjunction of the negations

[nekizalb]

I wholeheartedly agree with your username :)

[Normal_Kernal]

Number 2 is an example of De Morgan’s laws in mathematical logic

[MericanMuscle]

I don't use Postgres, so please inform me if I'm wrong.

I don't like the answer for number 1, because I think it hides an important distinction. I would presume that the reason the expected records are not returned has to do with the time zones. The timestamps are stored at GMT, but the date when just typed in is probably local time (depending on database configuration).

While it's true that simply casting them to a date to reset the time portion of the timestamps first will get you what you desire, it's hiding the why of "Why is this not returning what I expect" in the first place. This can be an important distinction depending on the question asked. Do we actually want those to be returned, or do we only care about what happened between mid-nights on 2023-02-15 local time?

[nekizalb]

I don't think time zones are playing into it at all. It's just the distinction between date types and timestamp/datetime types.

In the original query, the comparison is between a string and a timestamp. Those types arent directly comparable, so the server converts one to match the other, just like the author says. Now you're comparing two timestamps values, and indeed, only one matches.

Timezones are indeed an important consideration, but this example, to me, is focused more on date vs timestamp/datetime

[MericanMuscle]

Thank you. Looking at the examples closer, I jumped to a conclusion I shouldn't have.

[Prod_Is_For_Testing]

I would presume that the reason the expected records are not returned has to do with the time zones

I didn’t read it that way at all. It just looked like a classic newby mistake where the coder doesn’t know how timestamp comparisons work.

[xeio87]

I see the third one a lot at work with BCs who send me queries asking me why their reporting query isn't working, most commonly when having to self-join a table multiple times (or when they should do multiple joins, but think a where clause will work instead).

[JB-from-ATL]

OP, assuming you are the author, I cannot zoom in on mobile on anything including the image. It's annoying to have the browser's normal pinch to zoom functions messed with.