为google提醒查找给定期间的提醒的SQL查询

Question

我正在尝试一个表的设计和逻辑约会类似于谷歌日历提醒。

要求为多个用户存储单个和重复的提醒，并查找给定时间段的提醒，以便在日历中查看。

表: -任命

appointment_id,
user_id,
start_date,
end_date,   --> End date for the repeat reminder. Null means never ending
start_time,
end_time

表: -appointment_frequency

appointment_id,
frequency_type,  --> D(Daily),W(Weekly),M(Monthly),Y(Yearly)
interval, --> Every 3 days, Every 2 weeks, Every 4 Months, ...
sunday, --> Weekly interval that happens on Sundays
monday,
tuesday,
wednesday,
thursday,
friday,
saturday, --> Weekly interval that happens on Saturdays
on_day,   --> On 5th day of each Month, ...
on_week,  --> On 1st week of each Month, On 2nd week of each month
on_last_week, --> On last week of the month
max_repeat. --> Maximum repeat time

有更好的方法来存储这个吗?

现在，我需要找到给定时间段的提醒?例如，为用户1查找10月1日至15日之间的提醒事项&2 .

我目前，为用户获取所有提醒，然后运行java代码查找提醒b/w期间。是否有任何方法获得使用SQL查询?

http://sqlfiddle.com/!9/b540da/5

我要找的输出是，

reminder_id, user_id, name, Date, Start_time, End_time

对于没有结束日期的每日提醒，

当我说，给我提醒b/w 10/15/21和10/12/21，

它必须返回多个条目，因为每日提醒将在日历中每天发生。

Answer 1

你可以在mysql中创建一个PROCEDURE，或者你可以使用Java(因为这是你在评论中提到的编程语言)来循环遍历日期范围并创建一个UNION查询并运行一次。

String sqlstatement = "";
for (LocalDate date = startDate; date.isBefore(endDate); date = date.plusDays(1))
{
sqlstatement += "SELECT a.appointment_id as reminder_id, a.usr_id as user_id, '"+date+"' as date, a.st_time as Start_time, a.end_time as End_time FROM appointment a LEFT JOIN appointment_frquency af ON a.appointment_id = af.appointment_id WHERE '"+date+"' >= a.st_date AND '"+date+"' <= a.end_date UNION";
}
sqlstatement = sqlstatement.substring(0, sqlstatement.lastIndexOf(" UNION"));

在for循环之后，您需要从字符串中删除最后一个UNION，然后才能运行您的查询。