MySql 闰年

Question

我有一个名为 reviews 的 MySql 表，它存储了很多产品评论，这些评论是多年来积累的，每条评论都带有日期时间戳。
我想绘制一个条形图，显示我一年中每天积累的评论数量，希望找到对我的系统重要的日期。

我使用了这个查询

select dayofyear(date),count(*) from reviews group by dayofyear(date);

但是，在注意到返回的是 366 行而不是 365 行之后，我意识到我不能用它来制作这张图表，因为日索引在每个闰年都会偏移 1，这会扭曲我的数据。

例如，圣诞节在大多数年份显示为#359，但在闰年显示为#360。

解决这种偏差的最简单方法是什么？

旁注，是否有任何软件包可以接受 SQL 查询并将结果直接作为条形图返回(当条形图有意义时)

Answer 1

我还没有测试过这个，但你可以做类似下面的事情:

SELECT 
        # check if the year is a leap year:
        IF((YEAR(mydate) % 4 = 0 AND YEAR(mydate) % 100 != 0) OR YEAR(mydate) % 400 = 0,
            # if so, check if the date is before or after february 29th. if that is the case, we subtract one from the value
            IF(DAYOFYEAR(mydate) > DAYOFYEAR("2008-02-28"), DAYOFYEAR(mydate) - 1, DAYOFYEAR(mydate)),
            # if the year isn't a leap year, just return the regular dayofyear() value
            DAYOFYEAR(mydate))
    FROM mytbl

这将合并闰年 28 日和 29 日的数据，但为闰年和非闰年的所有其他日子提供相同的偏移量。更可取的行为可能是简单地忽略 2 月 29 日的数据，这可以使用附加条件来完成。您还可以为其分配一个特殊索引，例如 400，它不会抵消所有其他日期。

更好的方法可能是按月份和日期分组:

select month(date) m, day(date) d, count(*) from reviews group by m, d;

这一起避免了这个问题，但您在应用程序逻辑中的处理可能会更复杂。不过，我认为这是更好的方法。