mysql中常用的日期函数包括获取当前时间、格式化日期、日期计算、提取日期部分及unix时间戳转换；1. 使用now()、curdate()、curtime()和sysdate()获取当前日期时间，其中now()返回语句开始时间，sysdate()返回函数执行时间；2. date_format()用于将日期格式化为指定字符串，str_to_date()则将字符串解析为日期，常用于数据清洗；3. date_add()和date_sub()用于增减时间间隔，datediff()计算两日期间天数差，timestampdiff()可指定单位计算时间差；4. year()、month()、day()等函数用于提取日期的年、月、日等部分，weekday()和dayofweek()返回星期几，注意其起始日不同；5. unix_timestamp()将日期转为unix时间戳，from_unixtime()将时间戳转为日期时间；常见陷阱包括：在where子句中对日期字段使用函数会导致索引失效，应改用范围查询如>= and 隐式类型转换可能导致解析失败，应显式使用str_to_date()；null值处理需谨慎，函数遇null通常返回null，需结合is null或coalesce()进行判断；高效日期范围查询应避免在字段上使用函数以利用索引，推荐使用>= and 工具或load data infile进行清洗，必要时用regexp识别异常格式以确保数据质量。
MySQL中常用的日期函数主要围绕着获取当前时间、格式化日期、进行日期时间计算、以及从日期时间中提取特定部分等几大类。它们是我们在处理时间序列数据、进行复杂查询或报表统计时不可或缺的利器。说实话，如果没有这些函数，我们数据库里那些密密麻麻的时间戳和日期字段，恐怕就真成了摆设，难以发挥其真正的价值。
解决方案
要深入理解MySQL的日期函数，我们不妨从它们最常见的用途入手。
1. 获取当前日期和时间 这是最基础也是最常用的。
-
NOW()
: 返回当前的日期和时间，格式为 ‘YYYY-MM-DD HH:MM:SS’。它会根据SQL语句执行的精确时间点返回。
SELECT NOW(); -- 可能会得到 '2023-10-27 10:30:05'
-
CURDATE()
: 只返回当前日期，格式为 ‘YYYY-MM-DD’。
SELECT CURDATE(); -- 可能会得到 '2023-10-27'
-
CURTIME()
: 只返回当前时间，格式为 ‘HH:MM:SS’。
SELECT CURTIME(); -- 可能会得到 '10:30:05'
-
SYSDATE()
: 类似于
NOW()
，但它返回的是函数执行时的精确时间，而不是语句开始执行的时间。在某些高并发场景下，这细微的差别可能很重要。
SELECT SYSDATE();
2. 日期格式化与解析 这是处理日期数据时最容易出问题，但也最强大的部分。
-
DATE_FORMAT(date, format)
: 将日期格式化为指定字符串。
format
参数是关键，它定义了输出的格式。
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时%i分%s秒'); -- 结果：'2023年10月27日 10时30分05秒' SELECT DATE_FORMAT('2023-10-27 10:30:05', '%W, %M %D, %Y'); -- 结果：'Friday, October 27th, 2023' -
STR_TO_DATE(str, format)
: 将字符串解析为日期。这个函数在导入或清洗数据时尤其有用，因为它能识别各种非标准日期格式。
SELECT STR_TO_DATE('20231027', '%Y%m%d'); -- 结果：'2023-10-27' SELECT STR_TO_DATE('Oct 27, 2023', '%M %d, %Y'); -- 结果：'2023-10-27'如果字符串与格式不匹配，
STR_TO_DATE
会返回
NULL
，这在数据校验时是个不错的特性。
3. 日期计算 增减时间间隔，计算日期差。
-
DATE_ADD(date, INTERVAL expr unit)
或
ADDDATE(date, INTERVAL expr unit)
: 给日期增加一个时间间隔。
-
DATE_SUB(date, INTERVAL expr unit)
或
SUBDATE(date, INTERVAL expr unit)
: 从日期减去一个时间间隔。
unit
可以是
YEAR
,
MONTH
,
DAY
,
HOUR
,
MINUTE
,
SECOND
等等。
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); -- 明天 SELECT DATE_SUB(NOW(), INTERVAL 3 HOUR); -- 三小时前 SELECT ADDDATE('2023-01-01', INTERVAL 1 MONTH); -- 2023-02-01 SELECT SUBDATE(CURDATE(), INTERVAL 1 WEEK); -- 上周的今天 -
DATEDIFF(expr1, expr2)
: 计算两个日期之间的天数差。
expr1 - expr2
。
SELECT DATEDIFF('2023-10-30', '2023-10-27'); -- 结果：3 -
TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)
: 计算两个日期时间之间的差值，可以指定单位。
SELECT TIMESTAMPDIFF(MINUTE, '2023-10-27 10:00:00', '2023-10-27 10:30:00'); -- 结果：30 (分钟) SELECT TIMESTAMPDIFF(YEAR, '1990-05-15', CURDATE()); -- 计算年龄
4. 提取日期部分 从日期或时间中提取年、月、日等。
-
YEAR(date)
: 返回年份。
-
MONTH(date)
: 返回月份 (1-12)。
-
DAY(date)
/
DAYOFMONTH(date)
: 返回日 (1-31)。
-
HOUR(time)
: 返回小时 (0-23)。
-
MINUTE(time)
: 返回分钟 (0-59)。
-
SECOND(time)
: 返回秒 (0-59)。
-
WEEK(date)
/
WEEKOFYEAR(date)
: 返回周数。注意不同模式下周的起始日不同。
-
QUARTER(date)
: 返回季度 (1-4)。
-
DAYOFWEEK(date)
: 返回星期几 (1=周日, 7=周六)。
-
WEEKDAY(date)
: 返回星期几 (0=周一, 6=周日)。
5. Unix时间戳转换
-
UNIX_TIMESTAMP(date)
: 将日期时间转换为Unix时间戳（自1970-01-01 00:00:00 UTC以来的秒数）。
SELECT UNIX_TIMESTAMP(NOW());
-
FROM_UNIXTIME(unix_timestamp, format)
: 将Unix时间戳转换为日期时间。可以指定格式。
SELECT FROM_UNIXTIME(1678886400); -- 2023-03-15 00:00:00 SELECT FROM_UNIXTIME(1678886400, '%Y-%m-%d %H:%i:%s');
MySQL日期函数在数据查询与分析中的常见陷阱有哪些？
在使用MySQL日期函数时，我们确实会遇到一些让人头疼的问题，这些坑往往不是语法错误，而是性能或逻辑上的偏差。我个人觉得，最典型的几个陷阱，你一定要心里有数。
首先，索引失效是老生常谈，也是最致命的。很多时候，我们为了方便，会在
WHERE
子句中对日期字段使用函数。比如，你想查询某一天的所有订单，你可能会写成这样：
SELECT * FROM orders WHERE DATE_FORMAT(order_time, '%Y-%m-%d') = '2023-10-27';
或者
SELECT * FROM orders WHERE YEAR(order_time) = 2023 AND MONTH(order_time) = 10 AND DAY(order_time) = 27;
看起来很直观，对吧？但问题是，一旦你对
order_time
这个字段使用了函数，MySQL的优化器就无法直接利用
order_time
字段上的索引了。它不得不对表进行全扫描，然后对每一行数据都执行一次函数计算，再进行比较。想象一下，如果你的表有几百万甚至上亿条记录，这简直是灾难性的性能杀手。
正确的做法应该是避免在
WHERE
子句的左侧使用函数，而是将条件转化为范围查询：
SELECT * FROM orders WHERE order_time >= '2023-10-27 00:00:00' AND order_time < '2023-10-28 00:00:00';
这样，索引就能被高效地利用起来。
其次，时区问题也是一个隐形的雷。MySQL服务器有自己的时区设置，你的应用程序连接时也可能有自己的时区，而你存储的日期时间数据本身可能没有时区信息，或者有特定的时区。
NOW()
和
SYSDATE()
在某些情况下表现出的差异也与此有关。
NOW()
返回的是语句执行开始时的时区感知时间，而
SYSDATE()
返回的是函数调用时的时区感知时间。如果你在处理跨地域用户数据，或者需要严格按照UTC时间存储，但服务器设置的是本地时间，那么很可能出现数据不一致。我通常建议，如果可能，所有时间都统一存储为UTC时间戳（使用
UNIX_TIMESTAMP()
），在应用层再根据用户时区进行展示，这样可以最大程度避免时区混乱。
再来，隐式类型转换也是一个容易被忽视的坑。MySQL在某些情况下会尝试帮你进行类型转换，但这并不总是好事。比如，你可能有一个
VARCHAR
类型的字段存储了日期字符串，当你直接用日期函数去操作它时，MySQL会尝试将其转换为日期类型。如果格式不规范，转换失败就会返回
NULL
或者错误，导致查询结果不准确。
STR_TO_DATE()
就是为了解决这类问题而生的，但如果你没有显式使用它，而是依赖隐式转换，那你的数据质量可能会受到影响。我在项目里就遇到过因为日期字符串格式不统一，导致某个日期范围查询总是漏掉一部分数据，最后才发现是隐式转换失败，那些数据被当成了
NULL
。
最后，NULL值处理。很多日期函数在遇到
NULL
输入时，会直接返回
NULL
。这在逻辑上是正确的，但如果你的业务逻辑需要区分
NULL
和无效日期，或者需要对
NULL
进行特殊处理，那么你需要额外在查询中加入
IS NULL
或
COALESCE()
等判断。这虽然不是什么大问题，但在复杂查询中，如果忘记考虑
NULL
，可能会导致最终的统计结果偏离预期。
如何高效地进行日期范围查询与数据聚合？
高效地进行日期范围查询和数据聚合，是数据库性能优化的核心一环，尤其在处理大量时序数据时。我个人在实践中，总结出几条非常实用的原则。
首先，对于日期范围查询，核心思想是“避免函数，利用索引”。前面提到了，不要在
WHERE
子句的左侧使用函数。最推荐的方式是使用
BETWEEN
操作符，或者更精确的
>= AND <
组合。 例如，要查询2023年10月27日一天的订单：
-- 推荐方式，可利用索引 SELECT * FROM orders WHERE order_time >= '2023-10-27 00:00:00' AND order_time < '2023-10-28 00:00:00'; -- 另一种方式，也可用索引 SELECT * FROM orders WHERE order_time BETWEEN '2023-10-27 00:00:00' AND '2023-10-27 23:59:59';
我更倾向于第一种
>= AND <
的方式，因为它对于
DATETIME
或
TIMESTAMP
类型来说，边界定义更清晰，不容易因为秒、毫秒的精度问题导致数据遗漏。如果你只关心日期部分，但字段是
DATETIME
，那么
DATE(order_time) = '2023-10-27'
这种写法也是会使索引失效的。这时候，同样建议转换为范围：
order_time >= '2023-10-27' AND order_time < '2023-10-28'
。
其次，是数据聚合。我们经常需要按天、按月、按年，甚至按周来统计数据。
- 按天聚合：
SELECT DATE(order_time) AS order_day, COUNT(*) AS daily_orders FROM orders GROUP BY order_day;
这里虽然对
order_time
使用了
DATE()
函数，但通常聚合查询（
GROUP BY
）对性能的影响不如
WHERE
子句中那么大，因为它是在筛选完数据后才进行计算。不过，如果数据量特别大，且你需要对聚合结果再进行筛选，可以考虑先按范围筛选再聚合。
- 按月聚合：
SELECT DATE_FORMAT(order_time, '%Y-%m') AS order_month, SUM(amount) AS monthly_revenue FROM orders GROUP BY order_month ORDER BY order_month;
或者使用
YEAR()
和
MONTH()
组合：
SELECT YEAR(order_time) AS order_year, MONTH(order_time) AS order_month, COUNT(*) FROM orders GROUP BY order_year, order_month ORDER BY order_year, order_month;
我个人觉得
DATE_FORMAT
在生成可读性强的月份字符串时更方便，而
YEAR/MONTH
组合在需要数值型年份月份时更直接。
- 按周聚合： 这稍微复杂一点，因为周的定义在不同地区和业务场景下可能不同（周日或周一作为一周的开始）。
-- 假设周一是一周的开始，使用模式3（ISO 8601） SELECT YEARWEEK(order_time, 3) AS order_week, COUNT(*) AS weekly_orders FROM orders GROUP BY order_week ORDER BY order_week;
YEARWEEK()
函数的第二个参数非常重要，它决定了周的起始日和周数的计算方式。务必根据你的业务需求选择正确的模式。
为了进一步提升聚合查询的性能，除了保证日期字段有索引外，还可以考虑创建覆盖索引。如果你的聚合查询只需要用到
order_time
和
amount
字段，那么创建一个
INDEX(order_time, amount)
的索引，MySQL在执行查询时就无需回表，直接从索引中获取所有需要的数据，这能显著提升性能。
最后，对于一些非常复杂的、需要跨多个维度聚合的报表，如果实时查询压力太大，我会考虑使用物化视图或预计算表。也就是在后台定时运行聚合查询，将结果存储到一张新的表中，前端直接查询这张预计算好的表，这样可以大大减轻主库的压力，并提供极快的报表响应速度。这虽然不是日期函数本身的优化，但却是高效利用日期数据进行分析的常用策略。
遇到日期格式不规范的数据，我们该如何处理？
在实际的数据处理过程中，日期格式不规范的数据简直是家常便饭。这可能是因为数据源多样、人工录入错误、或者历史系统迁移等原因造成的。遇到这类问题，我的经验是，首先要冷静，然后采取“识别-清洗-验证”的步骤。
最核心的工具，毫无疑问是
STR_TO_DATE()
函数。它就是为了解决这类问题而生的。但问题是，如果你的数据里有多种不规范的日期格式，
STR_TO_DATE()
一次只能识别一种。
比如，你可能遇到这样的日期字符串：
-
2023-10-27
(标准格式)
-
2023/10/27
-
20231027
-
Oct 27, 2023
-
27-OCT-23
- 甚至还有
10/27/23
(美式月/日/年)
如果数据是混合的，你不能指望一个
STR_TO_DATE(date_str, '%Y-%m-%d')
就能搞定一切。这时候，我们需要更灵活的策略。
一种常见且有效的方法是结合
CASE
语句。你可以尝试用不同的
format
字符串去解析，直到成功为止。
SELECT CASE WHEN STR_TO_DATE(date_string_col, '%Y-%m-%d') IS NOT NULL THEN STR_TO_DATE(date_string_col, '%Y-%m-%d') WHEN STR_TO_DATE(date_string_col, '%Y/%m/%d') IS NOT NULL THEN STR_TO_DATE(date_string_col, '%Y/%m/%d') WHEN STR_TO_DATE(date_string_col, '%Y%m%d') IS NOT NULL THEN STR_TO_DATE(date_string_col, '%Y%m%d') WHEN STR_TO_DATE(date_string_col, '%M %d, %Y') IS NOT NULL THEN STR_TO_DATE(date_string_col, '%M %d, %Y') WHEN STR_TO_DATE(date_string_col, '%d-%M-%y') IS NOT NULL THEN STR_TO_DATE(date_string_col, '%d-%M-%y') -- 更多可能的格式... ELSE NULL -- 如果所有尝试都失败，则返回NULL END AS cleaned_date FROM your_table;
这种方法虽然有点冗长，但非常鲁棒。它会按顺序尝试各种格式，直到找到匹配的。如果所有格式都不匹配，那么它会返回
NULL
，这给了你一个明确的信号：这行数据的日期有问题。
在数据导入阶段，这尤其重要。如果你的数据是通过CSV或其他文件导入的，我强烈建议在导入前或导入过程中就进行清洗。例如，你可以在
LOAD DATA INFILE
语句中使用
STR_TO_DATE()
，或者在ETL工具中完成转换。把不规范的日期字符串直接存入
DATETIME
或
DATE
字段是不明智的，因为MySQL会直接报错或存入
NULL
，导致数据丢失或不完整。
对于那些实在无法通过
STR_TO_DATE()
解析的“奇葩”格式，你可能需要借助正则表达式（
REGEXP
）进行初步的筛选或验证。比如，你可以用正则表达式找出那些完全不符合日期模式的字符串，然后单独处理它们。这通常发生在数据质量非常差，甚至包含乱码的情况下。
-- 找出看起来不像标准日期的字符串 SELECT date_string_col FROM your_table WHERE date_string_col NOT
评论（已关闭）
评论已关闭