Getting records by date is multiples of 30 days

Question

I have the following query to get appointments that need remind once a month if they are not done yet. I want to get records with 30, 60, 90, 120,etc... in the past from the current date.

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.

Answer 1

If your primary concern is to speed up this query we can add a column int for comparing the number of days and index it. We then add triggers to calculate the modulus of the datediff between the start of the Unix period: 01/01/1970 (or any other date if you prefer) and store the result in this column.
This will take a small amount of storage space, and slow down insert and update operations. This will not be noticable when we add or modify one appointment at the time, which I suspect to be the general case.
When we query our table we calculate the day value of today, which will take very little time as it will only be done once, and compare it with the days column which will be very quick because it is indexed and there are no calculations involved.
Finally we run your current query and look at it using explain to see that, even though we have indexed the column date_ , the index cannot be used for this query.

CREATE TABLE appointments (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, 
  date_ date,
  days int 
  );
CREATE INDEX ix_apps_days ON appointments (days);

?

?

CREATE PROCEDURE apps_day()
BEGIN
UPDATE appointments SET days = day(date_);
END

?

CREATE TRIGGER t_apps_insert BEFORE INSERT ON appointments
    FOR EACH ROW
BEGIN
SET NEW.days = DATEDIFF(NEW.date_, '1970-01-01') % 30 ;
END;

?

CREATE TRIGGER t_apps_update BEFORE UPDATE ON appointments
    FOR EACH ROW
BEGIN
SET NEW.days = DATEDIFF(NEW.date_, '1970-01-01') % 30 ;
END;

?

insert into appointments (date_) values ('2022-01-01'),('2022-01-01'),('2022-04-15'),(now());

?

update appointments set date_ = '2022-01-12' where id = 1;

?

select * from appointments

id | date_      | days
-: | :--------- | ---:
 1 | 2022-01-12 |   14
 2 | 2022-01-01 |    3
 3 | 2022-04-15 |   17
 4 | 2022-04-22 |   24

select 
 *
from appointments
where DATEDIFF(CURDATE() , '1970-01-01') % 30 = days;

id | date_      | days
-: | :--------- | ---:
 4 | 2022-04-22 |   24

explain
select  DATEDIFF(CURDATE() , '1970-01-01')
from appointments
where DATEDIFF(CURDATE() , '1970-01-01') = days;

id | select_type | table        | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra      
-: | :---------- | :----------- | :--------- | :--- | :------------ | :----------- | :------ | :---- | ---: | -------: | :----------
 1 | SIMPLE      | appointments | null       | ref  | ix_apps_days  | ix_apps_days | 5       | const |    1 |   100.00 | Using index

CREATE INDEX ix_apps_date_ ON appointments (date_);

?

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

id | date_      | days
-: | :--------- | ---:
 4 | 2022-04-22 |   24

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

id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra      
-: | :---------- | :---- | :--------- | :--- | :------------ | :--- | :------ | :--- | ---: | -------: | :----------
 1 | SIMPLE      | a     | null       | ALL  | null          | null | null    | null |    4 |   100.00 | Using where

db<>fiddle here

Answer 2

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;

Answer 3

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.

Answer 4

Ok, lets all put the dates and date-diff aside for a moment. Looking at the question, the person is trying to look for all appointments in the past that dont necessarily have another in the future. Such as doing a FOLLOW-UP appointment with a Dr. "Come back in a month to see where things change". This points me to thinking there is probably some patient ID in the table of appointments. So this probably turns the question to looking at the past 30, 60 or 90 days ago to see if there was a corresponding appointment scheduled in the future. If already scheduled, the patient does not need a call reminder to get into the office.

That said, I would start a bit differently, get all patients that DID have an appointment within the last 90 days, and see if they already have (or not) a follow-up appointment already on the schedule for the follow-up. This way, the office person can make contacts with said patients to get on the calendar.

start by getting all maximum appointments for any given patient within the last 90 days. If someone had an appointment 90 days ago, and had a follow-up at 59 days, then they probably only care about the most recent appointment to make sure THAT has the follow-up.

select
      a1.patient_id,
      max( a1.appointment_date ) MostRecentApnt
   from 
      appointments a1
   WHERE
      a1.appointment_date > date_sub( a1.appointment_date, interval 90 day )
   group by
      a1.patient_id

Now, from this fixed list and beginning date, all we care is, how many days to current is there last appointment. IS it X number of days? Just use datediff and sort. You can visually see the how many days. By trying to break them into buckets of 30, 60 or 90 days, just knowing how many days since the last appointment is probably just as easy as sorting in DESCENDING order with the oldest appointments getting called on first, vs those that just happened. Maybe even cutting off the calling list at say 20 days and still has not made an appointment and getting CLOSE to the expected 30 days in question.

SELECT
      p.LastName,
      p.FirstName,
      p.Phone,
      Last90.Patient_ID,
      Last90.MostRecentApnt,
      DATEDIFF(CURDATE(), Last90.appointment_date)  LastAppointmentDays
   FROM
      ( select
              a1.patient_id,
              max( a1.appointment_date ) MostRecentApnt
           from 
              appointments a1
           WHERE
              a1.appointment_date > date_sub( a1.appointment_date, interval 90 day )
           group by
              a1.patient_id ) Last90
      -- Guessing you might want patient data to do phone calling
         JOIN Patients p
            on Last90.Patient_id = p.patient_id
   order by
      Last90.MostRecentApnt DESC,
      p.LastName,
      p.FirstName

Sometimes, having an answer just for the direct question doesnt get the correct need. Hopefully I am more on-target with the desired ultimate outcome needs. Again, the above implies joining to the patient table for follow-up call purposes to schedule an appointment.