Get first event oncoming from recurring events

[CreatoX]

Hi All,

 

I'll try to formulate my question as easy as possible.

Presume I have the following events in a table:

(Dateformat: DD-MM-YYYY HH:mm)

 

• 01-01-2017 12:00 Event 1 (Sunday) WEEKLY
• 09-01-2017 12:00 Event 2 (Monday) ONCE

 

Let say, today is Saturday 07-01-2017. 

 

How can I query the table to get the first oncoming event is the Weekly "Event 1" on Sunday and not get the event on Monday?

 

The best solution for me would be to get the 'real' next event date for the recurring events. So the output of a select statement will be:

 

• 08-01-2017 12:00 Event 1 (Sunday) WEEKLY
• 09-01-2017 12:00 Event 2 (Monday) ONCE

Is there a way to accomplish this within mysql querying ?

 

[Barand]

You are not going to accomplish anything that relies on date sequencing with your dates in that format. You need to use DATETIME type column with format of YYYY-MM-DD HH-ii_ss so the dates are sortable.

[CreatoX]

Hi All,

 

I'll try to formulate my question as easy as possible.

Presume I have the following events in a table:

(Dateformat: YYYY-MM-DD HH-mm)

 

• 2017-01-01 12-00 Event 1 (Sunday) WEEKLY
• 2017-01-09 12-00 Event 2 (Monday) ONCE

 

Let say, today is Saturday 2017-01-07. 

 

How can I query the table to get the first oncoming event is the Weekly "Event 1" on Sunday and not get the event on Monday?

 

The best solution for me would be to get the 'real' next event date for the recurring events. So the output of a select statement will be:

 

• 2017-01-08 12-00 Event 1 (Sunday) WEEKLY
• 2017-01-09 12-00 Event 2 (Monday) ONCE

Is there a way to accomplish this within mysql querying ?

[Barand]

You need to calculate how many days from now to the recurrence of the next weekday for any recurring events. So if it's a weekly event on mondays then the next will be three days from now.

 

So if we have

mysql> select * from event order by start_date;
+----------+---------------+------------+------------------+--------+
| event_id | event_name    | start_date | event_venue      | recurs |
+----------+---------------+------------+------------------+--------+
|        2 | Halle concert | 2016-07-01 | Bridgewater Hall | WEEKLY |
|        4 | PHP Group     | 2016-08-10 | Greyhound Inn    | WEEKLY |
|        1 | Flower show   | 2016-09-08 | Tatton Park      | ONCE   |
|        3 | Rock festival | 2016-10-22 | Bramall Hall     | ONCE   |
+----------+---------------+------------+------------------+--------+

then we can get the next events with

SELECT event_name
, recurs
, start_date
, DAYNAME(start_date) as day
, CASE recurs
    WHEN 'WEEKLY' THEN CURDATE() + INTERVAL (7 + WEEKDAY(start_date) - WEEKDAY(CURDATE())) DAY
    WHEN 'ONCE' THEN start_date
  END as next_date
FROM event
HAVING next_date > CURDATE()
ORDER BY next_date;

+---------------+--------+------------+-----------+------------+
| event_name    | recurs | start_date | day       | next_date  |
+---------------+--------+------------+-----------+------------+
| PHP Group     | WEEKLY | 2016-08-10 | Wednesday | 2016-10-05 |
| Halle concert | WEEKLY | 2016-07-01 | Friday    | 2016-10-07 |
| Rock festival | ONCE   | 2016-10-22 | Saturday  | 2016-10-22 |
+---------------+--------+------------+-----------+------------+