mySQL Repeating Events Query

Question

There are some solutions, but i don't know how to change that for my table. So i hope someone could help me.

I have the following Table

╔═════╦═════════╦════════════╦════════════╦═══════════╗
║ UID ║  TITLE  ║   BEGIN    ║    END     ║ RECURRING ║
╠═════╬═════════╬════════════╬════════════╬═══════════╣
║   1 ║ event A ║ 1359741600 ║ 1359745200 ║ none      ║
║   1 ║ event B ║ 1359741600 ║          0 ║ daily     ║
║   1 ║ event C ║ 1359741600 ║          0 ║ weekly    ║
║   1 ║ event D ║ 1359741600 ║          0 ║ monthly   ║
║   1 ║ event E ║ 1359741600 ║          0 ║ yearly    ║
╚═════╩═════════╩════════════╩════════════╩═══════════╝

How can i now select every event from now on up to 7 days and also all recurring events in the next 7 days?

The following i've tried but not working very good and not finished.

SELECT 
  * 
FROM
  `tx_events_domain_model_event` 
WHERE 
  /* none recurring events in the next 7 days */
  (
    recuring = 'none' 
    AND (begin_date + begin_time) >= UNIX_TIMESTAMP(NOW()) 
    AND (end_date + end_time) <= UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL 7 DAY))
  ) 
  OR 
  /* Daily */
  recuring = 'daily' 
  OR 
  /* Weekly */
  (
    recuring = 'weekly' 
    AND DAYOFWEEK(NOW()) - 1 <= DAYOFWEEK(FROM_UNIXTIME(begin_date)) - 1
  ) 
  OR 
  /* Monthly */
  (recuring = 'monthly' 
  AND

Welcome to SO :) user1960507 +1 for the question and +1 for JW. for requesting expected results :) — bonCodigo, Jan 09 '13 at 07:47
@JW: i need help to create the query (or get the final query) to get all events (including recurring events) of a day (like the current day or any other day i specify)... thanks — HR123, Jan 09 '13 at 10:07

score 2 · Answer 1 · answered Jan 14 '13 at 03:32

Here's something I've been playing with (and here it is as an sqlfiddle with some sample data)... not 100% sure about it, but it should grab the last 7 days of data. Note that I'm using MySQL DATETIME versus integer timestamps, but you should be able to convert that easily (for testing the query it was much easier to use string dates).

SELECT *
  FROM
    (SELECT 
      *, 
      CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-', DAY(start)) AS monthly,
      CONCAT(YEAR(NOW()), '-', MONTH(start), '-', DAY(start)) AS yearly
      FROM events
    ) tmp
  WHERE
    (
      (recurring = 'none')
      OR (recurring = 'daily')
      OR (recurring = 'weekly')
      OR (
        recurring = 'monthly'
        AND (
          (
            monthly >= NOW()
            AND monthly <= DATE_ADD(NOW(), INTERVAL 7 DAY)
          )
          OR (
            DATE_ADD(monthly, INTERVAL 1 MONTH) >= NOW()
            AND DATE_ADD(monthly, INTERVAL 1 MONTH) <= DATE_ADD(NOW(), INTERVAL 7 DAY)
          )
        )
      )
      OR (
        recurring = 'yearly'
        AND (
          (
            yearly >= NOW()
            AND yearly <= DATE_ADD(NOW(), INTERVAL 7 DAY)
          )
          OR (
            DATE_ADD(yearly, INTERVAL 1 YEAR) >= NOW()
            AND DATE_ADD(yearly, INTERVAL 1 YEAR) <= DATE_ADD(NOW(), INTERVAL 7 DAY)
          )
        )
      )
    )
    AND start <= NOW()
    AND (
      end IS NULL 
      OR end >= DATE_ADD(NOW(), INTERVAL 7 DAY)
    )

score 0 · Answer 2 · answered Jul 05 '15 at 14:44

Here is my code which I have used for my reminder table.

You can just pass your date in the function and it will find whether the seleted date has event or not. It will give your all events which is recurring event or non-recurring event.

-- 
-- Table structure for table `reminder`
-- 

CREATE TABLE `reminder` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `startdate` datetime NOT NULL,
  `enddate` date NOT NULL,
  `kind` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- 
-- Dumping data for table `reminder`
-- 

INSERT INTO `reminder` (`id`, `title`, `startdate`, `enddate`, `kind`) VALUES 
(1, 'This is reminder', '2015-06-03 19:25:44', '2015-07-21', 'weekly'); 


-- Query data get results for 2015-06-17 from  `reminder` table
SELECT CONCAT_WS(' ','2015-06-17',TIME(startdate)) AS ReminderDateTime, title
                  FROM reminder  
                  WHERE IF(
                                        kind='daily',
                                        ((UNIX_TIMESTAMP('2015-06-17') -UNIX_TIMESTAMP(DATE(startdate))) % (1*24*60*60)=0)  ,
                                        IF(
                                            kind='weekly',
                                            ((UNIX_TIMESTAMP('2015-06-17') -UNIX_TIMESTAMP(DATE(startdate))) % (7*24*60*60)=0)  ,
                                            IF(
                                                kind='monthly',
                                                DAYOFMONTH('2015-06-17')=DAYOFMONTH(startdate),                         
                                                IF(
                                                    kind='quarterly',
                                                    ((UNIX_TIMESTAMP('2015-06-17') -UNIX_TIMESTAMP(DATE(startdate))) % (91*24*60*60)=0) ,
                                                    IF(
                                                        kind='yearly',
                                                        DAYOFYEAR('2015-06-17')=DAYOFYEAR(startdate),   
                                                        IF(
                                                            kind='',
                                                            startdate,
                                                            '0000-00-00 00:00:00'
                                                        )
                                                    )   
                                                )   
                                            )   
                                         )
                                        ) AND DATE(startdate) <='2015-06-17' AND IF(enddate<>'0000-00-00',enddate>='2015-06-17','1') ORDER BY ReminderDateTime ASC

score 0 · Answer 3 · answered Jul 17 '18 at 20:02

Found solution with help of fellow programmer Aaron Hanson. The issue is with daylight savings. Here is an updated query...

SELECT 
  CONCAT_WS(
    ' ', 
    '2018-07-17', 
    TIME(start_time)
  ) AS ShiftDateTime 
FROM 
  schedule 
WHERE 
  IF(
    repeat_type = 'daily', 
    '2018-07-17', 
    IF(
      repeat_type = 'weekly', 
      DAYOFWEEK('2018-07-17') = DAYOFWEEK(start_date), 
      IF(
        repeat_type = 'monthly', 
        DAYOFMONTH('2018-07-17') = DAYOFMONTH(start_date), 
        IF(
          repeat_type = 'quarterly', 
          (
            (
              MONTH('2018-07-17') - MONTH(
                DATE(start_date)
              )
            ) % 3 = 0
          ), 
          IF(
            repeat_type = 'yearly', 
            DAYOFYEAR('2018-07-17') = DAYOFYEAR(start_date), 
            IF(
              repeat_type = NULL, start_date, '0000-00-00 00:00:00'
            )
          )
        )
      )
    )
  ) 
  AND DATE(start_date) <= '2018-07-17' 
  AND IF(
    repeat_end_date <> '0000-00-00', repeat_end_date >= '2018-07-17', 
    '1'
  ) 
ORDER BY 
  ShiftDateTime ASC

mySQL Repeating Events Query

3 Answers3

Linked