Getting records by date is multiples of 30 days-CodePudding

I have the following query to get appointments that need remind once a month if they are not done yet.

SELECT
    a.*
FROM
    appointments a
WHERE
    DATEDIFF(CURDATE(), a.appointment_date) % 30 = 0

is there another way not to use DATEDIFF to achieve this? I want to increase the performance of this query.

CodePudding user response：

You could use the following query which compares the day of the month of the appointement to the day of the month of today.
We also test whether we are the last day of the month so as to get appointements due at the end of the month. For example if we are the 28th February (not a leap year) we will accept days of the month >= 28, ie 29, 30 & 31, which would otherwise be missed.
This method has the same problem as your current system, that appointements falling during the weekend will be missed.

select a.*
from appointements a,
(select 
  day(now()) today,
  case when day(now())= last_day(now()) then day(now()) else 99 end lastDay
) days
where d = today or d >= lastDay;

CodePudding user response：

You just want the appointments for 30 days in the future? Are they stored as DATE? Or DATETIME? Well, this works in either case:

SELECT ...
    WHERE appt_date >= CURDATE()   INTERVAL 30 DAY
      AND appt_date  < CURDATE()   INTERVAL 31 DAY

If you have INDEX(appt_date) (or any index starting with appt_date), the query will be efficient.

Things like DATE() are not "sargable", and prevent the use of an index.

If your goal is to nag customers, I see nothing in your query to prevent nagging everyone over and over. This might need a separate "nag" table, where customers who have satisfied the nag can be removed. Then performance won't be a problem, since the table will be small.