重复日历事件和一些最终的数学运算 [英] Repeating calendar events and some final maths
问题描述
我正在尝试使用PHP/MySQL处理日历上臭名昭著的重复事件.我终于找到了似乎可行的方法.我在这里找到了答案,但是我在完成它时遇到了一些困难.
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.
我的第一个表事件".
ID NAME
1 Sample Event
2 Another Event
我的第二个表'events_meta,用于存储重复数据.
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 */
其中repeat_start是没有时间的日期作为unix时间戳,而repeat_interval是间隔之间的秒数(432000是5天).
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).
然后我有了下面的MySQL,我从上面的链接中对其进行了一些修改.下面使用的时间戳记(2012年5月12日为1299132000)是没有时间的当天.
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
在上述MySQL中,以下代码从当前日期中减去repeat_start字段(EM1.'meta_value'
),然后将其除以重复间隔字段(EM2.'meta_value'
).
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
所以这是数学:
1336744800 - 1336312800 = 432000
432000 / 432000 = 1
现在可以完美运行了.但是,如果我将当前时间戳提前5天更改为1336312800(即2012年7月17日),它看起来会像这样:
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
哪个不起作用是因为它等于2,而在MySQL中它必须等于1.所以我想我的问题是,如何让MySQL识别整数而不是必须这样做?
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,....)
希望我很有道理,我希望这只是简单的数学事物或MySQL可以提供帮助的功能:)谢谢您的帮助!
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!
答案
感谢下面的@eggypal,我找到了答案,这当然很简单!
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
推荐答案
目前尚不清楚您要查询的内容 ,但是问题的实质使我倾向于建议您看起来转换为模块化算术:在SQL中,当a
除以b
时,a % b
返回 remainder -如果没有余数(即a % b = 0
),则a
必须为b
的精确倍数.
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
.
在您的情况下,我认为,您正在尝试查找事件,该事件从事件开始到给定文字之间的时间是事件间隔的精确倍数:即(literal - event_start) % event_interval = 0
.如果它不为零,则该值是literal
之后下一次出现的时间(因此,要确定下一次出现是否在某个时间段(例如一天)内发生,可以测试一下是否剩余的时间是小于这样的常数,例如(literal - event_start) % event_interval < 86400
.
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.
这篇关于重复日历事件和一些最终的数学运算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!