MySQL 日期函数大全

DATE_ADD

基本语法

DATE_ADD(date, INTERVAL expr unit)

功能

在指定日期/时间上添加一个时间间隔

参数说明

• date：要处理的日期/时间值（可以是DATE, DATETIME或TIMESTAMP类型）
• expr：要添加的间隔数值（可以是正数或负数）
• unit：时间单位（见下方支持的单位）

  支持的时间单位

  MICROSECOND | SECOND | MINUTE | HOUR | DAY 
  | WEEK | MONTH | QUARTER | YEAR 
  | SECOND_MICROSECOND | MINUTE_MICROSECOND 
  | MINUTE_SECOND | HOUR_MICROSECOND 
  | HOUR_SECOND | HOUR_MINUTE 
  | DAY_MICROSECOND | DAY_SECOND 
  | DAY_MINUTE | DAY_HOUR 
  | YEAR_MONTH
  

  使用示例

  -- 加1天
  SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY);  -- 结果: 2023-01-02
  -- 加1个月
  SELECT DATE_ADD('2023-01-31', INTERVAL 1 MONTH); -- 结果: 2023-02-28 (自动处理月末)
  -- 加1年
  SELECT DATE_ADD('2020-02-29', INTERVAL 1 YEAR);  -- 结果: 2021-02-28 (闰年自动调整)
  -- 加3小时30分钟
  SELECT DATE_ADD('2023-01-01 12:00:00', INTERVAL '3:30' HOUR_MINUTE); -- 结果: 2023-01-01 15:30:00
  -- 使用负数表示减法
  SELECT DATE_ADD('2023-01-01', INTERVAL -1 DAY);  -- 结果: 2022-12-31
  

  DATE_SUB

  基本语法

  DATE_SUB(date, INTERVAL expr unit)
  

  功能

  与DATE_ADD相反，从指定日期/时间减去一个时间间隔

  使用示例

  -- 减1天
  SELECT DATE_SUB('2023-01-01', INTERVAL 1 DAY);  -- 结果: 2022-12-31
  -- 减1个月
  SELECT DATE_SUB('2023-03-31', INTERVAL 1 MONTH); -- 结果: 2023-02-28
  -- 等同于DATE_ADD使用负数
  SELECT DATE_SUB('2023-01-01', INTERVAL 1 DAY) = DATE_ADD('2023-01-01', INTERVAL -1 DAY); -- 结果: 1(TRUE)
  

  简写语法

  MySQL还提供更简洁的日期加减写法：

  -- 加1天
  SELECT '2023-01-01' + INTERVAL 1 DAY;
  -- 减1个月
  SELECT '2023-01-01' - INTERVAL 1 MONTH;
  

  实际应用场景

  场景1：计算到期日

  -- 贷款30天后到期
  SELECT loan_date, DATE_ADD(loan_date, INTERVAL 30 DAY) AS due_date 
  FROM loans;
  

  场景2：查找最近7天的记录

  SELECT * FROM orders 
  WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY);
  

  场景3：处理订阅续期

  -- 订阅续期1年
  UPDATE subscriptions 
  SET end_date = DATE_ADD(end_date, INTERVAL 1 YEAR)
  WHERE user_id = 1001;
  

  注意事项

  1. 自动调整无效日期：

     SELECT DATE_ADD('2023-01-31', INTERVAL 1 MONTH); -- 返回2023-02-28
     

  2. 闰年处理：

     SELECT DATE_ADD('2020-02-29', INTERVAL 1 YEAR); -- 返回2021-02-28
     

  3. 时间溢出处理：

     SELECT DATE_ADD('2023-01-01 23:59:59', INTERVAL 1 SECOND); -- 返回2023-01-02 00:00:00
     

  4. 性能考虑：在WHERE条件中对列使用这些函数会导致索引失效

  与其他日期函数的对比

  需求	推荐函数	示例
  计算未来/过去特定时间点	DATE_ADD/DATE_SUB	DATE_ADD(now(), INTERVAL 1 HOUR)
  计算两个日期的差值	DATEDIFF/TIMESTAMPDIFF	DATEDIFF(end_date, start_date)
  提取日期部分	DATE_FORMAT/EXTRACT	EXTRACT(YEAR FROM date)
  获取当前日期/时间	NOW/CURDATE	SELECT CURDATE(), NOW()

  DATEDIFF

  

  DATEDIFF 函数解释

  DATEDIF(datepart, startdate, enddate) 是一个日期函数，用于计算两个日期之间的时间差。

  参数说明

  1. datepart - 指定要计算的时间单位，常用值包括：

     • year 或 yy - 年

     • quarter 或 qq - 季度

     • month 或 mm - 月

     • day 或 dd - 日

     • week 或 wk - 周

     • hour 或 hh - 小时

     • minute 或 mi - 分钟

     • second 或 ss - 秒

  2. startdate - 开始日期

  3. enddate - 结束日期

  返回值

  函数返回从 startdate 到 enddate 之间指定 datepart 单位的整数差值。

  示例

  -- 计算两个日期之间的天数差
  SELECT DATEDIFF(day, '2023-01-01', '2023-01-31') 
  -- 返回 30
  -- 计算两个日期之间的月份差
  SELECT DATEDIFF(month, '2023-01-15', '2023-03-20') 
  -- 返回 2 (即使不足整月也按整月计算)
  

  注意事项

  • 在SQL Server中函数名为 DATEDIFF，在其他数据库系统中可能有不同名称或语法

  • 结果总是整数，会截断小数部分

  • enddate 应晚于 startdate，否则返回负值

  DATE_FORMAT

  在 MySQL 中，DATE_FORMAT() 函数用于将日期或时间值格式化为指定的字符串格式。

  正确语法

  DATE_FORMAT(date, format)
  

  常用格式说明符

  说明符	描述
  %Y	四位数的年份（例如：2023）
  %y	两位数的年份（例如：23）
  %m	两位数的月份（01-12）
  %d	两位数的日期（01-31）
  %H	24小时制的小时（00-23）
  %i	分钟（00-59）
  %s	秒（00-59）

  示例用法

  1. 获取四位数的年份：

  SELECT DATE_FORMAT(created_at, '%Y') FROM your_table;
  

  1. 获取完整的日期时间格式：

  SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') FROM your_table;
  

  1. 获取月份和年份：

  SELECT DATE_FORMAT(created_at, '%M %Y') FROM your_table;
  -- 结果示例："May 2023"
  

  1. 在你的例子中，如果要提取年份：

  SELECT DATE_FORMAT(created_at, '%Y') AS year FROM your_table;
  

  注意事项

  • MySQL 的 DATE_FORMAT() 函数使用百分号 (%) 作为格式说明符的前缀
  • 函数名是 DATE_FORMAT 而不是 dateformt（注意大小写不敏感但拼写要正确）
  • 第一个参数是日期/时间列或表达式，第二个参数是格式字符串