Repeating calendar events and some final maths

Question

I am trying to have a go at the infamous repeating events on calendars using PHP/MySQL. I've finally found something that seems to work. I found my answer here but I'm having a little difficulty finishing it off.

My first table 'events'.

ID    NAME
1     Sample Event
2     Another Event

My second table 'events_meta that stores the repeating data.

ID    event_id      meta_key           meta_value
1     1             repeat_start       1336312800 /* May 7th 2012 */
2     1             repeat_interval_1  432000 /* 5 days */

With repeat_start being a date with no time as a unix timestamp, and repeat_interval an amount in seconds between intervals (432000 is 5 days).

I then have the following MySQL which I modified slightly from the above link. The timestamp used below (1299132000 which is 12th May 2012) is the current day with no time.

SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN `events_meta` EM2 ON EM2.`meta_key` = CONCAT( 'repeat_interval_', EM1.`id` )
WHERE EM1.meta_key = 'repeat_start'
    AND (
        ( CASE ( 1336744800 - EM1.`meta_value` )
            WHEN 0
              THEN 1
            ELSE ( 1336744800 - EM1.`meta_value` ) / EM2.`meta_value`
          END
        )
    ) = 1

In the above MySQL, the following code deducts the repeat_start field (EM1.'meta_value') from the current date and then divides it by the repeat interval field (EM2.'meta_value').

ELSE ( 1336744800 - EM1.`meta_value` ) / EM2.`meta_value`

OR

TODAYS DATE - START DATE / 5 DAYS

So here's the maths:

1336744800 - 1336312800 = 432000
432000 / 432000 = 1

Now that works perfect. But if I change the current timestamp 5 days ahead to 1336312800 which is 17th Mat 2012, it looks a bit like this:

1336312800 - 1336312800 = 864000
86400 / 432000 = 2

Which doesn't work because it equals 2 and in the MySQL it needs to equal 1. So I guess my question is, how do I get the MySQL to recognise a whole number rather than having to do this?

...
WHERE EM1.meta_key = 'repeat_start'
    AND (
        ( CASE ( 1336744800 - EM1.`meta_value` )
            WHEN 0
              THEN 1
            ELSE ( 1336744800 - EM1.`meta_value` ) / EM2.`meta_value`
          END
        )
    ) = IN (1,2,3,4,5,6,7,8,....)

Hope I'm making sense and I hope it's just a simple maths thing or a function that MySQL has that will help :) Thanks for your help!

EDIT: THE ANSWER

Thanks to @eggypal below, I found my answer and of course it was simple!

SELECT EV.*
FROM elvanto_calendars_events AS EV
RIGHT JOIN elvanto_calendars_events_meta AS EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN elvanto_calendars_events_meta AS EM2 ON EM2.`meta_key` = CONCAT( 'repeat_interval_', EM1.`id` )
WHERE EM1.meta_key = 'repeat_start'
AND ( ( 1336744800 - EM1.`meta_value` ) % EM2.`meta_value`) = 0

Date math based on timestamps and fixed number of seconds in a day will break on Daylight Savings. — DCoder, May 12 '12 at 06:31
Good call @DCoder. I store all my times as GMT and then use the CONVERT_TZ function to determine the `repeat_start` date. The above code examples doesn't include this extra code. I think that should do the trick right? — Ben Sinclair, May 13 '12 at 23:26
will your query allow me to see the result for the given start date and end date? `1336744800 ` looks like a specific day :( — Murali Murugesan, Nov 29 '13 at 12:31
I started following it and failed http://stackoverflow.com/questions/20286332/display-next-event-date — Billa, Nov 29 '13 at 14:25

eggyal · Accepted Answer · 2015-10-19T08:23:49.777

10

It's not entirely clear what you want your query to do, but the jist of your question makes me lean toward suggesting that you look into modular arithmetic: in SQL, a % b returns the remainder when a is divided by b - if there is no remainder (i.e. a % b = 0), then a must be an exact multiple of b.

In your case, I think you're trying to find events where the time between the event start and some given literal is an exact multiple of the event interval: that is, (literal - event_start) % event_interval = 0. If it's non-zero, the value is the time to the next occurrence after literal (and, therefore, to determine whether that next occurrence occurs within some period of time, say a day, one would test to see if the remainder is less than such constant e.g. (literal - event_start) % event_interval < 86400).

If this isn't what you're after, please clarify exactly what your query is trying to achieve.

edited Oct 19 '15 at 08:23

answered May 11 '12 at 11:05

eggyal

122,705
18
212
237

That's the trick! I managed to get it working in PHP: `echo ((strtotime('2012-05-22 00:00:00')- 1336312800) % 432000);` I tried using the `%` in MySQL but it didn't work. Is their an equivalent? – Ben Sinclair May 12 '12 at 05:47
Sorry, ignore my last comment, it worked! I'll edit my original post with the final code to help others out :) – Ben Sinclair May 12 '12 at 05:51
I started following it and failed. Help me. http://stackoverflow.com/questions/20286332/display-next-event-date – Billa Nov 29 '13 at 14:24

score -1 · Answer 2 · answered Nov 23 '22 at 16:55

set @dat_ini = '2023-05-20',@dat_fim = '2022-11-20'; select (DATEDIFF( @dat_fim,@dat_ini )) % 60

THIS < 10

It only works for a short period.

To do this, take the start date and change the Month that is on the screen and add a year, then subtract it from the start date, then it works.

Repeating calendar events and some final maths

2 Answers2

Linked