mysql – 如何使用这个复杂的表结构按日期和时间订购我的项目？
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 如何使用这个复杂的表结构按日期和时间订购我的项目？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6065字，纯文字阅读大概需要9分钟。
内容图文
我正在使用一个可怕的适当CMS系统.
其事件模块可以通过明确的日期/时间来存储事件,例如, 2011-08-04 13:30:00或者通过每周重复发生的事件,其中将重复日存储为基于0的整数,并且将时间添加到日期/时间字段(其中数据为0),例如, <br> 13:30:00="" <="" p="" 我不是一个sql专家.我写了这个查询……="" 我需要在接下来的两周内完成所有活动.这意味着我需要所有每周重复活动以及接下来两周内的明确日期.="">
SELECT `id`,
`name`,
`info`,
`date_time`,
`weekly_day`
FROM `events` AS a
WHERE `active` = true
AND `id` IN (SELECT `id`
FROM `events`
WHERE WEEK(`date_time`) BETWEEN WEEK(NOW()) AND WEEK(
DATE_ADD(NOW(), INTERVAL
1 WEEK)))
OR DATE(`date_time`) = 0
ORDER BY Time_to_sec(IF(TIME(`date_time`), TIME(`date_time`),
Concat(EXTRACT(HOUR FROM `date_time`
), ":",
EXTRACT(MINUTE FROM `date_time`)))),
IF(DATE(`date_time`), Dayofweek(`date_time`), `weekly_day` + 1) ASC
(注意上面的NOW()被一些PHP替换,并且回显了MySQL格式的当前日期.MySQL服务器的时区与我想要的时区不同.)
这成功获取了我想要的所有记录,但它没有正确排序.例如,6月9日的活动将在6月2日的活动之前出现.
如何修复查询？
干杯.
更新
我想出了这个,但我仍然被卡住了……尽管日期似乎是正确的,但它不会显示每周的事件.
SELECT `id`,
`name`,
`info`,
`date_time`,
`weekly_day`,
( DATE(`date_time`) = 0 ) AS `weekly`
FROM `events`
WHERE `active` = true
AND IF (DATE(`date_time`), WEEK(`date_time`),
DATE_ADD(
Concat(DATE(NOW()), " ", Concat(EXTRACT(HOUR FROM `date_time`),
":",
EXTRACT
(
MINUTE FROM `date_time`))), INTERVAL
`weekly_day`
DAY))
BETWEEN WEEK(
NOW()) AND WEEK(DATE_ADD(NOW(), INTERVAL 2 WEEK))
ORDER BY `date_time` ASC
我无法使用TIME()来提取时间部分,因为它一直返回NULL.
我究竟做错了什么？
解决方法:
我会分两步完成：
>将定期条目转换为接下来两周的两个DATETIME值.这将是一个子查询.
>收集同一时期的非经常性条目.这将是第二个子查询.
两个子查询的UNION为您提供所有事件及其实际发生日期和时间;正确地命令是微不足道的.
如果您需要记录信息是否来自定期会议,请在两个子查询的选择列表中保留一些内容(可能是简单的“R”或“N”),这样您就可以判断原始条目是否为经常性或非经常性.
我不是MySQL DATETIME操作的专家 – 但我确实知道我在另一个DBMS的那个区域,即Informix.就本次讨论而言,表中只有两个有趣的列：date_time和weekly_day.我假设您使用的是1 =星期日,7 =星期六的MySQL约定.由于我们需要2周的重复事件信息,因此最简单的生成3周并过滤掉不相关的事件.
给定参考日期和一周中的整数天,我们可以使用以下三个值：
refdate - DAYOFWEEK(refdate) + day_of_week
refdate - DAYOFWEEK(refdate) + day_of_week + INTERVAL 7 DAYS
refdate - DAYOFWEEK(refdate) + day_of_week + INTERVAL 14 DAYS
这产生三个日期;第一个可能在过去,或者第三个可能在将来太远(因此需要丢弃).这是日期是自参考日期以来的天数(如在Informix中)的算术.要将第三行转换为MySQL,假设’refdate’是NOW(),我们似乎必须写(非常冗长 – 我认为Informix对于DATETIME操作非常冗长)：
DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())), INTERVAL weekly_day DAY),
INTERVAL 14 DAY)
在这里,我假设我可以在week_day中将整数转换为若干天;必要时调整.这些公式为每个重复条目提供三个DATE值,其中两个是相关的.为了增加时间,我认为我们需要：
DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())), INTERVAL weekly_day DAY),
INTERVAL 14 DAY), INTERVAL TIME(date_time) HOUR_SECOND)
因此,我的初始答案中提到的第一个子查询本身需要是一个3路UNION.
SELECT 'R' AS info_mode,
DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
INTERVAL weekly_day DAY),
INTERVAL 14 DAY),
INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
FROM events
UNION
SELECT 'R' AS info_mode,
DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
INTERVAL weekly_day DAY),
INTERVAL 7 DAY),
INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
FROM events
UNION
SELECT 'R' AS info_mode,
DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
INTERVAL weekly_day DAY),
INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
FROM events;
您现在需要过滤“接下来的两周”.究竟是哪个日期/时间范围？从参考时刻 – 指定NOW()？或者从参考日开始？或者从参考日后的第二天开始？一切都可能合理;因为代码似乎使用引用模式(并且它是最简单的),我们将继续：
SELECT *
FROM (SELECT 'R' AS info_mode,
DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
INTERVAL weekly_day DAY),
INTERVAL 14 DAY),
INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
FROM events
UNION
SELECT 'R' AS info_mode,
DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
INTERVAL weekly_day DAY),
INTERVAL 7 DAY),
INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
FROM events
UNION
SELECT 'R' AS info_mode,
DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
INTERVAL weekly_day DAY),
INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
FROM events) AS r
WHERE r.event_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 14 DAY);
这给出了重复发生的事件. r.event_time上的过滤条件可以在UNION的每个分支中被下推(复制),但我会将它保留在临时区域.
使用以下内容找到非重复发生的事件：
SELECT 'N' AS info_mode,
date_time AS event_time
FROM events AS e
WHERE e.date_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 14 DAY)
AND DATE(e.date_time) != DATE '0000-00-00';
因此,我们可以创建这两个查询的并集.可以在两个子查询中拖出event_time上的条件(我们可以希望优化器然后’把它放回’).
并且非重复事件子查询只是成为UNION的第四部分,导致：
SELECT *
FROM (SELECT 'R' AS info_mode,
DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
INTERVAL weekly_day DAY),
INTERVAL 14 DAY),
INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
FROM events
UNION
SELECT 'R' AS info_mode,
DATE_ADD(DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
INTERVAL weekly_day DAY),
INTERVAL 7 DAY),
INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
FROM events
UNION
SELECT 'R' AS info_mode,
DATE_ADD(DATE_ADD(DATE_SUB(NOW(), DAYOFWEEK(NOW())),
INTERVAL weekly_day DAY),
INTERVAL TIME(date_time) HOUR_SECOND) AS event_time
FROM events
UNION
SELECT 'N' AS info_mode,
date_time AS event_time
FROM events
WHERE DATE(e.date_time) != DATE '0000-00-00'
) AS e
WHERE e.event_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 14 DAY);
因此,这应该为您提供事件类型列表(重复,非重复)以及在接下来的14天内发生的事件类型.您只需要在UNION中的4个查询中添加id,name和info列(以及用于调试的原始date_time和weekly_day列),并在e.event_time上添加ORDER BY子句(以及任何tie-打破要添加的列).
请关注任何语法或DBMS特定的语义错误;我希望这个概念是清晰的(并且是正确的),即使由于不熟悉MySQL的细节而导致细节不正确.
内容总结
以上是互联网集市为您收集整理的mysql – 如何使用这个复杂的表结构按日期和时间订购我的项目？全部内容，希望文章能够帮你解决mysql – 如何使用这个复杂的表结构按日期和时间订购我的项目？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。