MySQL日期函数：DAYOFMONTH()与DAYOFYEAR()详解
各位同学，大家好！今天我们来详细探讨MySQL中两个非常实用的日期函数：DAYOFMONTH()和DAYOFYEAR()。这两个函数分别用于提取日期在一个月和一年中的天数。理解和熟练运用它们，可以帮助我们更灵活地处理日期数据，进行各种复杂的日期计算和分析。
DAYOFMONTH()函数
DAYOFMONTH()函数用于返回指定日期在一个月中的天数，返回值范围是1到31。其基本语法如下：
DAYOFMONTH(date)其中，date参数可以是一个日期或日期时间表达式。它可以是以下几种类型：
DATEDATETIMETIMESTAMP
如果date参数为NULL，则DAYOFMONTH()函数返回NULL。
示例：
-
从日期字符串中提取：
SELECT DAYOFMONTH('2023-10-27'); -- 输出：27 SELECT DAYOFMONTH('2024-02-15'); -- 输出：15 -
从日期时间字符串中提取：
SELECT DAYOFMONTH('2023-10-27 10:30:00'); -- 输出：27 -
从
DATE类型的列中提取：假设我们有一个名为
orders的表，其中有一个order_date列，类型为DATE。CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE ); INSERT INTO orders (order_id, order_date) VALUES (1, '2023-10-05'), (2, '2023-11-12'), (3, '2023-12-25'); SELECT order_id, order_date, DAYOFMONTH(order_date) AS day_of_month FROM orders;查询结果如下：
order_id order_date day_of_month 1 2023-10-05 5 2 2023-11-12 12 3 2023-12-25 25 -
从
DATETIME类型的列中提取：假设我们有一个名为
events的表，其中有一个event_time列，类型为DATETIME。CREATE TABLE events ( event_id INT PRIMARY KEY, event_time DATETIME ); INSERT INTO events (event_id, event_time) VALUES (1, '2023-10-05 14:00:00'), (2, '2023-11-12 09:30:00'), (3, '2023-12-25 18:00:00'); SELECT event_id, event_time, DAYOFMONTH(event_time) AS day_of_month FROM events;查询结果如下：
event_id event_time day_of_month 1 2023-10-05 14:00:00 5 2 2023-11-12 09:30:00 12 3 2023-12-25 18:00:00 25 -
结合其他函数使用：
例如，我们可以结合
NOW()函数获取当前日期在一个月中的天数。SELECT DAYOFMONTH(NOW()); -- 输出当前日期在一个月中的天数 -
处理
NULL值：如果
date参数为NULL，则DAYOFMONTH()函数返回NULL。SELECT DAYOFMONTH(NULL); -- 输出：NULL INSERT INTO orders (order_id, order_date) VALUES (4, NULL); SELECT order_id, order_date, DAYOFMONTH(order_date) AS day_of_month FROM orders WHERE order_id = 4;查询结果：
order_id order_date day_of_month 4 NULL NULL
DAYOFYEAR()函数
DAYOFYEAR()函数用于返回指定日期在一年中的天数，返回值范围是1到366。其基本语法如下：
DAYOFYEAR(date)其中，date参数的要求与DAYOFMONTH()函数相同，可以是一个日期或日期时间表达式，类型可以是DATE、DATETIME或TIMESTAMP。如果date参数为NULL，则DAYOFYEAR()函数返回NULL。
示例：
-
从日期字符串中提取：
SELECT DAYOFYEAR('2023-10-27'); -- 输出：300 SELECT DAYOFYEAR('2024-02-15'); -- 输出：46 (2024是闰年) -
从日期时间字符串中提取：
SELECT DAYOFYEAR('2023-10-27 10:30:00'); -- 输出：300 -
从
DATE类型的列中提取：继续使用之前的
orders表。SELECT order_id, order_date, DAYOFYEAR(order_date) AS day_of_year FROM orders;查询结果如下：
order_id order_date day_of_year 1 2023-10-05 278 2 2023-11-12 316 3 2023-12-25 359 4 NULL NULL -
从
DATETIME类型的列中提取：继续使用之前的
events表。SELECT event_id, event_time, DAYOFYEAR(event_time) AS day_of_year FROM events;查询结果如下：
event_id event_time day_of_year 1 2023-10-05 14:00:00 278 2 2023-11-12 09:30:00 316 3 2023-12-25 18:00:00 359 -
结合其他函数使用：
例如，我们可以结合
NOW()函数获取当前日期在一年中的天数。SELECT DAYOFYEAR(NOW()); -- 输出当前日期在一年中的天数 -
处理
NULL值：如果
date参数为NULL，则DAYOFYEAR()函数返回NULL。SELECT DAYOFYEAR(NULL); -- 输出：NULL
应用场景
DAYOFMONTH()和DAYOFYEAR()函数在实际应用中非常广泛。以下是一些常见的应用场景：
-
生成日历报表： 可以使用
DAYOFMONTH()函数将日期数据按月份进行分组，生成日历报表，显示每个月每天的数据统计。例如，假设我们有一个名为
sales的表，其中包含销售日期sale_date和销售额amount。CREATE TABLE sales ( sale_id INT PRIMARY KEY, sale_date DATE, amount DECIMAL(10, 2) ); INSERT INTO sales (sale_id, sale_date, amount) VALUES (1, '2023-10-01', 100.00), (2, '2023-10-05', 150.00), (3, '2023-10-15', 200.00), (4, '2023-10-27', 120.00), (5, '2023-11-03', 180.00), (6, '2023-11-10', 250.00), (7, '2023-11-20', 130.00); SELECT DAYOFMONTH(sale_date) AS day, SUM(amount) AS total_amount FROM sales WHERE YEAR(sale_date) = 2023 AND MONTH(sale_date) = 10 GROUP BY day ORDER BY day;查询结果如下：
day total_amount 1 100.00 5 150.00 15 200.00 27 120.00 -
计算日期范围： 可以使用
DAYOFYEAR()函数计算某个日期是一年中的第几天，从而确定日期在一年中的位置，进行日期范围的筛选。例如，要查询
orders表中一年中前100天的订单。SELECT order_id, order_date FROM orders WHERE DAYOFYEAR(order_date) <= 100; -
统计特定日期类型的数据： 可以结合
DAYOFMONTH()或DAYOFYEAR()函数，统计特定日期类型的数据。例如，统计每个月的最后一天发生的事件数量。
SELECT MONTH(event_time) AS month, COUNT(*) AS event_count FROM events WHERE DAYOFMONTH(event_time) = DAY(LAST_DAY(event_time)) GROUP BY month ORDER BY month; -
数据分析： 在数据分析中，
DAYOFMONTH()和DAYOFYEAR()函数可以帮助我们分析数据的季节性变化、周期性规律等。例如，分析每个月或每个季度的销售额变化情况。SELECT QUARTER(sale_date) AS quarter, SUM(amount) AS total_amount FROM sales GROUP BY quarter ORDER BY quarter; -
数据清洗： 在数据清洗过程中，可以使用这两个函数来检查日期数据的完整性和一致性。例如，检查是否存在无效的日期，如2月30日。
SELECT * FROM your_table WHERE MONTH(date_column) = 2 AND DAYOFMONTH(date_column) > 29; --查找2月份超过29天的记录
与其他日期函数的比较
-
DAYOFWEEK()：DAYOFWEEK()函数返回指定日期是一周中的第几天（1表示星期日，2表示星期一，以此类推）。与DAYOFMONTH()和DAYOFYEAR()不同，DAYOFWEEK()关注的是星期几。 -
WEEKDAY()：WEEKDAY()函数返回指定日期是一周中的第几天（0表示星期一，1表示星期二，以此类推）。与DAYOFWEEK()类似，但起始日不同。 -
MONTH()：MONTH()函数返回指定日期的月份（1到12）。 -
YEAR()：YEAR()函数返回指定日期的年份。 -
DATE()：DATE()函数提取日期或日期时间表达式的日期部分。 -
EXTRACT()：EXTRACT()函数可以提取日期或日期时间表达式的各个部分，包括年、月、日、小时、分钟、秒等。
| 函数 | 功能 | 返回值范围 |
|---|---|---|
DAYOFMONTH() |
返回指定日期在一个月中的天数 | 1到31 |
DAYOFYEAR() |
返回指定日期在一年中的天数 | 1到366 |
DAYOFWEEK() |
返回指定日期是一周中的第几天（1表示星期日） | 1到7 |
WEEKDAY() |
返回指定日期是一周中的第几天（0表示星期一） | 0到6 |
MONTH() |
返回指定日期的月份 | 1到12 |
YEAR() |
返回指定日期的年份 | 取决于日期 |
DATE() |
提取日期或日期时间表达式的日期部分 | YYYY-MM-DD格式的日期字符串 |
EXTRACT() |
提取日期或日期时间表达式的各个部分 | 取决于提取的部分，如年、月、日、小时、分钟、秒 |
注意事项
-
数据类型： 确保传递给
DAYOFMONTH()和DAYOFYEAR()函数的参数是有效的日期或日期时间表达式。否则，函数可能返回错误的结果或NULL。 -
时区： 在处理日期时间数据时，要注意时区的影响。MySQL服务器的时区设置可能会影响日期函数的返回值。可以使用
SET time_zone = '+08:00';设置时区。 -
闰年：
DAYOFYEAR()函数会考虑闰年，因此在闰年中，2月29日的DAYOFYEAR()返回值会比非闰年大1。 -
NULL值处理： 如果传递给函数的参数是
NULL，则函数返回NULL。在编写SQL语句时，要注意处理NULL值的情况。
代码示例：综合应用
假设我们有一个名为activities的表，记录了用户在网站上的活动。表结构如下：
CREATE TABLE activities (
activity_id INT PRIMARY KEY,
user_id INT,
activity_type VARCHAR(50),
activity_time DATETIME
);
INSERT INTO activities (activity_id, user_id, activity_type, activity_time) VALUES
(1, 101, 'login', '2023-10-01 08:00:00'),
(2, 101, 'post', '2023-10-05 12:30:00'),
(3, 102, 'login', '2023-10-10 10:00:00'),
(4, 101, 'comment', '2023-10-15 15:45:00'),
(5, 102, 'post', '2023-10-20 09:00:00'),
(6, 103, 'login', '2023-10-27 11:15:00'),
(7, 101, 'login', '2023-11-03 14:00:00'),
(8, 102, 'comment', '2023-11-10 16:30:00'),
(9, 103, 'post', '2023-11-17 10:45:00'),
(10, 101, 'login', '2023-11-24 13:00:00');现在，我们希望分析用户的活动情况，统计每个月每天的活动数量，以及每个用户在一年中的活动天数。
-- 统计每个月每天的活动数量
SELECT
YEAR(activity_time) AS year,
MONTH(activity_time) AS month,
DAYOFMONTH(activity_time) AS day,
COUNT(*) AS activity_count
FROM activities
GROUP BY year, month, day
ORDER BY year, month, day;
-- 统计每个用户在一年中的活动天数
SELECT
user_id,
COUNT(DISTINCT DAYOFYEAR(activity_time)) AS activity_days
FROM activities
WHERE YEAR(activity_time) = 2023 -- 可以指定年份
GROUP BY user_id
ORDER BY user_id;通过以上示例，我们可以看到DAYOFMONTH()和DAYOFYEAR()函数在数据分析中的强大作用。
总结
今天我们学习了MySQL中的DAYOFMONTH()和DAYOFYEAR()函数，它们可以帮助我们提取日期在一个月和一年中的天数。这两个函数在日期计算、数据分析和报表生成等领域都有广泛的应用。 掌握它们，能更有效地处理日期数据。