Question 306157 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How would I find records in the future

If a slot is recurring it happens at the same time each week.

How would I fetch records scheduled later in the day, week, or month, considering the current date and time? I am using MySQL.

My current query is

SELECT *
FROM `timetable`
WHERE (`week` >= {$week} OR `recurring` = 1)
    AND `day` >= {$day}
    AND `hour` >= {$hour}
LIMIT 3
Tags:

2

Answers


  1. 0

    Presumably you would want to include all weekly-recurring events, since there will always be one ahead of your current week/day/hour. I also assume you would want all events that come after the current week, irrespective of the day, and within the current week, you would want all the events that come after the current day, irrespective of the hour.

    If this is all correct, then your condition becomes:

    WHERE `recurring` = 1
OR `week` >= {$week} AND (
    `week` > {$week}
    OR `day` >= {$day} AND (
        `day` > {$day}
        OR `hour` >= {$hour}
    )
)
    Login or Signup to reply.
  2. 0

    Later in the day

    SELECT *
FROM `timetable`
WHERE `recurring` = 1 AND
      DATE_ADD(`the_time`, INTERVAL 7 DAY) BETWEEN NOW() AND CAST(DATE_ADD(NOW(), INTERVAL 1 DAY) AS DATE)

    Explanation:

    • we search for recurring events
    • we are interested in events between the current moment and the start of next day
    • the start of next date is computed by adding a day to this moment and converting it to date
    • I named your time column the_time, use yours instead of this one

    Later in the week

    SELECT *
FROM `timetable`
WHERE `recurring` = 1 AND
      DATE_ADD(`the_time`, INTERVAL 7 DAY) BETWEEN NOW() AND CAST(DATE_ADD(NOW(), INTERVAL (7 - WEEKDAY(NOW())) DAY) AS DATE)

    Here the logic is the same, except for the end of the interval, where we add as many days as the number of days still left from the week. We add 7 for Monday, 6 for Tuesday, etc.

    Now, WEEKDAY returns 0 for Monday, 1 for Tuesday, etc.

    So, 7 – WEEKDAY will result in the exact difference we want.

    Later in the Month

    SELECT *
FROM `timetable`
WHERE `recurring` = 1 AND
      DATE_ADD(`the_time`, INTERVAL 7 DAY) BETWEEN NOW() AND DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY)

    Similar query, but with the start of the next month.

    General remarks

    If you may have events at midnight, then you might want to subtract a second from the rightmost ends of the interval or use <= and < instead of between. I avoided those for the sake of understandability.

    Also, this way you find recurring events, but you find them once. So, if you have a Friday night Karaoke each Friday night and it is scheduled to happen twice from the remainder of this month, this query will find it once.

    If you want to show the recurring events for month view as many times they are scheduled for the month, you can use unioned queries or stored procedures.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search