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

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

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()

UNIX_TIMESTAMP()

再来，隐式类型转换也是一个容易被忽视的坑。MySQL在某些情况下会尝试帮你进行类型转换，但这并不总是好事。比如，你可能有一个

VARCHAR

NULL

STR_TO_DATE()

NULL

最后，NULL值处理。很多日期函数在遇到

NULL

NULL

NULL

NULL

IS NULL

COALESCE()

NULL

如何高效地进行日期范围查询与数据聚合？

高效地进行日期范围查询和数据聚合，是数据库性能优化的核心一环，尤其在处理大量时序数据时。我个人在实践中，总结出几条非常实用的原则。

首先，对于日期范围查询，核心思想是“避免函数，利用索引”。前面提到了，不要在

WHERE

BETWEEN

>= AND <

-- 推荐方式，可利用索引
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'

ChatX翻译

最实用、可靠的社交类实时翻译工具。 支持全球主流的20+款社交软件的聊天应用，全球200+语言随意切换。 让您彻底告别复制粘贴的翻译模式，与世界各地高效连接！

下载

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

• 按天聚合：

  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)

最后，对于一些非常复杂的、需要跨多个维度聚合的报表，如果实时查询压力太大，我会考虑使用物化视图或预计算表。也就是在后台定时运行聚合查询，将结果存储到一张新的表中，前端直接查询这张预计算好的表，这样可以大大减轻主库的压力，并提供极快的报表响应速度。这虽然不是日期函数本身的优化，但却是高效利用日期数据进行分析的常用策略。

遇到日期格式不规范的数据，我们该如何处理？

在实际的数据处理过程中，日期格式不规范的数据简直是家常便饭。这可能是因为数据源多样、人工录入错误、或者历史系统迁移等原因造成的。遇到这类问题，我的经验是，首先要冷静，然后采取“识别-清洗-验证”的步骤。

最核心的工具，毫无疑问是

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()

DATETIME

DATE

NULL

对于那些实在无法通过

STR_TO_DATE()

REGEXP

-- 找出看起来不像标准日期的字符串
SELECT date_string_col
FROM your_table
WHERE date_string_col NOT