MySQL中常用的日期函数有哪些 MySQL日期时间函数全集详解-mysql教程-PHP中文网

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 <；时区设置不一致可能引发数据偏差，建议统一存储utc时间；隐式类型转换可能导致解析失败，应显式使用str_to_date()；null值处理需谨慎，函数遇null通常返回null，需结合is null或coalesce()进行判断；高效日期范围查询应避免在字段上使用函数以利用索引，推荐使用>= and <或between；数据聚合时可按天使用date()、按月使用date_format('%y-%m')或year/month组合、按周使用yearweek()并指定模式；为提升性能可创建覆盖索引或使用物化视图预计算结果；面对不规范日期格式，应采用str_to_date()结合case语句尝试多种格式解析，按顺序匹配直至成功，失败则返回null，并可在导入阶段通过etl工具或load data infile进行清洗，必要时用regexp识别异常格式以确保数据质量。

MySQL中常用的日期函数有哪些 MySQL日期时间函数全集详解

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'

登录后复制

。

怪兽AI数字人

数字人短视频创作，数字人直播，实时驱动数字人

查看详情

其次，是数据聚合。我们经常需要按天、按月、按年，甚至按周来统计数据。

按天聚合：
```
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

登录后复制

以上就是MySQL中常用的日期函数有哪些 MySQL日期时间函数全集详解的详细内容，更多请关注php中文网其它相关文章！

大家都在看：

mysql中如何排查客户端连接异常如何在mysql中配置服务自动启动如何在mysql中使用事务处理日志数据如何在mysql中定期审计用户权限如何在mysql中优化安装参数