mysqlsqldatedatediff

DATEDIFF vs (w1.date = w2.date +1) difference? MySQL syntax

I was working on a SQL database question using MySQL. The goal is to find all IDs that satisfy today is warmer than yesterday. I'll show you my original code, which passed 2 out of 3 test cases and then a revised code which satisfies all 3.

What is the functional difference between these two? Is it a MySQL thing, leetcode thing, or something else?

Original

SELECT DISTINCT w2.id
FROM weather w1, weather w2
WHERE w2.RecordDate = w1.RecordDate +1 AND w2.temperature > w1.temperature

Revised

SELECT DISTINCT w2.id
FROM weather w1, weather w2
WHERE DATEDIFF(w2.RecordDate,w1.RecordDate) =1 AND w2.temperature > w1.temperature

The only differences is the use of DATEDIFF or use of w2.recordDate = w1.recordDate + 1.

I'd like to know, what is the difference between these two?

Edit: here's the LC problem https://leetcode.com/problems/rising-temperature/

Solution

  • This does not do what you want:

    w2.RecordDate = w1.RecordDate + 1

    Because you are using number arithmetics on date, this expression implicitly converts the dates to numbers, adds 1 to one of them, and then compares the results. Depending on the exact dates, it might work sometimes, but it is just a wrong approach. As an example, say your date is '2020-01-31', then adding 1 to it would produce integer number 20200132.

    MySQL understands date arithmetics, so I would use:

    w2.RecordDate = w1.RecordDate + interval 1 day