数据库日期处理函数和相关技巧

前言

MySQL 在日期和时间处理方面提供了丰富的函数和灵活的用法。

1. 时间处理函数有哪些？案例与代码示例

2. 日期和时间计算差值

• 距离今天多少天：

  SELECT DATEDIFF(NOW(), '2024-05-20') AS days_passed;

• 距离某时间多少小时：

  SELECT TIMESTAMPDIFF(HOUR, '2024-05-31 08:00:00', NOW()) AS hours_passed;

• 距离某时间多少秒：

  SELECT TIMESTAMPDIFF(SECOND, '2024-06-01 15:00:00', NOW()) AS seconds_passed;

3. 日期查询的相关案例和技巧

• 查询本月数据：

  SELECT * FROM 表名
  WHERE DATE_FORMAT(日期字段, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m');

• 查询最近7天数据：

  SELECT * FROM 表名
  WHERE 日期字段 >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

• 查询某一天的数据：

  SELECT * FROM 表名
  WHERE DATE(日期字段) = '2024-06-01';

4. 日期时间计算的使用（获取年、月、日、时间）

• 获取年、月、日、小时、分钟、秒：

  SELECT
  YEAR(NOW()) AS 年,
  MONTH(NOW()) AS 月,
  DAY(NOW()) AS 日,
  HOUR(NOW()) AS 小时,
  MINUTE(NOW()) AS 分钟,
  SECOND(NOW()) AS 秒;

5. 月初与月末、年前年尾的计算

• 月初：

  SELECT DATE_FORMAT(NOW(), '%Y-%m-01') AS 月初;

• 月末：

  SELECT LAST_DAY(NOW()) AS 月末;

• 年初：

  SELECT CONCAT(YEAR(NOW()), '-01-01') AS 年初;

• 年末：

  SELECT CONCAT(YEAR(NOW()), '-12-31') AS 年末;

6. 日期月份计算方式和技巧

• 获取上个月、下个月：

  -- 上个月
  SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m') AS 上个月;
  -- 下个月
  SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH), '%Y-%m') AS 下个月;

• 月份加减：

  SELECT DATE_ADD('2024-06-01', INTERVAL 2 MONTH) AS 两个月后;

7. 星期几怎么计算？（星期一为1，星期日为7）

MySQL 的 WEEKDAY() 函数返回 0（星期一）到 6（星期日），可通过加1调整为 1~7：

SELECT WEEKDAY('2024-06-01') + 1 AS week_day;
-- 结果：6（2024-06-01 是星期六）

如果需要显示中文星期几：

SELECT
  CASE WEEKDAY(NOW()) + 1
    WHEN 1 THEN '星期一'
    WHEN 2 THEN '星期二'
    WHEN 3 THEN '星期三'
    WHEN 4 THEN '星期四'
    WHEN 5 THEN '星期五'
    WHEN 6 THEN '星期六'
    WHEN 7 THEN '星期日'
  END AS 星期几;

8. 实战案例：月考勤、周考勤

• 月考勤统计（统计本月打卡天数）：

  SELECT user_id, COUNT(DISTINCT DATE(打卡时间)) AS 本月打卡天数
  FROM 打卡表
  WHERE DATE_FORMAT(打卡时间, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')
  GROUP BY user_id;

• 周考勤统计（统计本周打卡天数）：

  SELECT user_id, COUNT(DISTINCT DATE(打卡时间)) AS 本周打卡天数
  FROM 打卡表
  WHERE YEARWEEK(DATE(打卡时间), 1) = YEARWEEK(CURDATE(), 1)
  GROUP BY user_id;

补充技巧

• 日期格式化输出：

  SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s') AS 格式化时间;

• 灵活组合函数实现复杂需求，如跨月、跨年统计等。
  在实际业务中，常常需要统计跨月、跨年等复杂时间区间的数据。可以通过组合使用 DATE_FORMAT、YEAR()、MONTH()、DATE_ADD、DATE_SUB 等函数实现。例如：

• 跨月统计（如统计最近两个月的数据）：

  SELECT *
  FROM 表名
  WHERE 日期字段 >= DATE_SUB(CURDATE(), INTERVAL 2 MONTH);

• 跨年统计（如统计去年和今年的数据）：

  SELECT *
  FROM 表名
  WHERE YEAR(日期字段) IN (YEAR(CURDATE()), YEAR(CURDATE()) - 1);

• 统计某时间段内每月的数据量：

  SELECT DATE_FORMAT(日期字段, '%Y-%m') AS 月份, COUNT(*) AS 数量
  FROM 表名
  WHERE 日期字段 BETWEEN '2023-01-01' AND '2024-06-01'
  GROUP BY 月份;

通过这些函数的组合，可以灵活应对各种复杂的日期统计和查询需求。