Stu Stu - 3 months ago 6
MySQL Question

SQL CURRENT_DATE not gathering result I expect

Three key bits of detail.


  1. I am doing this directly via the SQL query function in phpMyAdmin

  2. The query being run is as below:

    SELECT * FROM `leaderboard`
    WHERE date > (CURRENT_DATE() - 30)
    ORDER BY time DESC

  3. My data is as follows:

    id room team time fte date
    46 adod Old But Amazing 372 0 2016-07-04 15:13:56
    40 adod The Inappropriates 315 0 2016-08-22 15:11:58
    41 adod Chelsea Hens 254 0 2016-08-22 15:12:13
    42 adod Serco-Top Of The Board 221 0 2016-08-22 15:12:44
    43 adod Gradually Escaping 165 0 2016-08-22 15:13:05
    44 adod The Lamb Marlborough 105 0 2016-08-22 15:13:29
    45 adod Failure 0 1 2016-08-22 15:13:38



This is all of the data in my table but is also the exact response I get when I run this query. What confuses me is that the current date (2016-08-16) is at least 40 days beyond 2016-07-04 so why is ID 46 being pulled through as a valid result?

Update: Also, a query of

SELECT * FROM `leaderboard`
WHERE date > (CURRENT_DATE() - 20)
ORDER BY time DESC


will not return ID 46

Answer

Instead of:

(CURRENT_DATE() - 30)

Use:

SUBDATE(CURRENT_DATE(), 30)

This is because the first expression uses the current date as a number in the YYYYMMDD format, i.e. 20160822, and then subtracts 30 from that, giving 20160792, and consequently unexpected results.