当前位置：文江博客话题详情

查找从上周日到下周日的活动

发布于 2024-11-07 21:47:00 字数 185 浏览 0 评论 0原文

如何找到本周以及下周日的 MySQL 数据？

给定一个日期（例如Wednesday 5/18/11），它将显示从上周日到下周日的事件。 5/15/11 到 5/22/11。

诀窍是找到给定日期的“前一个”星期日。

这怎么能做到呢？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

深府石板幽径 2024-11-14 21:47:00

SELECT * 
FROM   events 
WHERE  Yearweek(`eventdate`) = Yearweek(NOW()) 
        OR ( Weekday(NOW()) = 6 
             AND Yearweek(`eventdate`) = Yearweek( 
                 DATE_SUB(NOW(), INTERVAL 1 DAY)) )

SELECT * 
FROM   events 
WHERE  Yearweek(`eventdate`) = Yearweek(NOW()) 
        OR ( Weekday(NOW()) = 6 
             AND Yearweek(`eventdate`) = Yearweek( 
                 DATE_SUB(NOW(), INTERVAL 1 DAY)) )

回复收藏 0 原文

亢潮 2024-11-14 21:47:00

取自 Pentium 的答案，进行一些调整...

SELECT
    *
FROM
    Events
WHERE
    YEARWEEK(`eventdate`) = YEARWEEK(NOW()) OR
    (
        WEEKDAY(`eventdate`) = 6 AND
        YEARWEEK(`eventdate`) = YEARWEEK(NOW()) + 1
    )

这可能需要根据 WEEKDAY 的值进行调整（是 6 号星期日？）。

另外，虽然这应该可行，但我的猜测是 mySQL 将无法通过此方法使用 eventdate 列上的任何索引。最好找到相邻星期日的实际日期本身，然后执行 BETWEEN 或 <= >=。这应该允许在事件日期上使用索引。即使您现在没有索引，您将来也可能想使用它。

Taking from Pentium's answer, with some adjustments...

SELECT
    *
FROM
    Events
WHERE
    YEARWEEK(`eventdate`) = YEARWEEK(NOW()) OR
    (
        WEEKDAY(`eventdate`) = 6 AND
        YEARWEEK(`eventdate`) = YEARWEEK(NOW()) + 1
    )

This may need to be adjusted depending on the values for WEEKDAY (is 6 Sunday?).

Also, while this should work, my guess is that mySQL won't be able to use any indexes on the eventdate column with this method. It's probably better to find the actual dates themselves for the bordering Sundays and then do a BETWEEN or <= >=. This should allow the use of an index on the eventdate. Even if you don't have an index on it now, you might want to use one in the future.

回复收藏 0 原文

杀手六號 2024-11-14 21:47:00

使用日历表。。。

select cal_date
from calendar
where cal_date between 
                 (select max(cal_date) from calendar
                  where cal_date <= '2011-05-15' and day_of_week = 'Sun') and
                 (select min(cal_date) from calendar
                  where cal_date > '2011-05-15' and day_of_week = 'Sun')

如果给定的日期是星期日，则不清楚您想要什么。前面的查询返回 15 行（给定日期为星期日）。它返回所有其他日期的 8 行。您可以调整 WHERE 子句中的比较运算符以获得您想要的行为。

我发布了日历表的代码早些时候关于SO。它适用于 PostgreSQL，但您应该能够轻松地将其适应 MySQL。

Using a calendar table . . .

select cal_date
from calendar
where cal_date between 
                 (select max(cal_date) from calendar
                  where cal_date <= '2011-05-15' and day_of_week = 'Sun') and
                 (select min(cal_date) from calendar
                  where cal_date > '2011-05-15' and day_of_week = 'Sun')

It's not clear what you want if the given date is a Sunday. This previous query returns 15 rows given a date that falls on Sunday. It returns 8 rows for all other days. You can tweak the comparison operators in the WHERE clause to get the behavior you want.

I posted code for a calendar table earlier on SO. It's for PostgreSQL, but you should be able to adapt it to MySQL without much trouble.

回复收藏 0 原文

~没有更多了~