发布于 由admin

MySQL编程进阶之：日期与时间函数的魔法：如何进行复杂的日期计算与格式化。

各位观众老爷们，大家好！我是你们的老朋友，今天咱们来聊聊MySQL里那些让人又爱又恨的日期和时间函数。别害怕，它们其实没那么难搞，掌握了它们，你就能像个时间旅行者一样，在数据库里玩转各种日期，格式化成你想要的任何样子！

开场白：时间都去哪儿了？（以及MySQL怎么帮你找回来）

咱们先来个小剧场：

你（抓狂）：老板，我要查上个月的销售额！

MySQL（淡定）：SELECT SUM(amount) FROM sales WHERE sale_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE();

你（一脸懵）：这是啥？

MySQL（微笑）：这就是时间魔法！

所以，今天咱们就来学习这些时间魔法，让你也能像MySQL一样，轻松应对各种日期时间的需求。

第一幕：时间的基石 – 基本的日期时间类型

在开始魔法之前，咱们得先认识一下舞台上的演员 – MySQL的日期时间类型。

数据类型 描述 格式 范围
DATE 日期，只包含年、月、日 YYYY-MM-DD 1000-01-01 到 9999-12-31
TIME 时间，只包含时、分、秒 HH:MM:SS -838:59:59 到 838:59:59
DATETIME 日期和时间的组合 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 到 9999-12-31 23:59:59
TIMESTAMP 时间戳，存储自1970-01-01 00:00:00 UTC以来的秒数，与时区相关。 YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC (32位系统限制)
YEAR 年份，可以是2位或4位数字 YYYY 或 YY 1901 到 2155 (4位)， 70 到 69 (2位，转换为1970-2069)

注意事项：

  • TIMESTAMP 会根据服务器的时区自动转换，而 DATETIME 不会。
  • TIMESTAMP 的范围有限，要注意别超出范围。
  • YEAR(2) 已经过时，不建议使用。

第二幕：时间的魔法棒 – 常用的日期时间函数

有了演员，咱们就得给他们配上魔法棒 – MySQL的日期时间函数。 这些函数能让你轻松获取当前时间、提取日期部分、进行日期计算等等。

1. 获取当前日期时间

  • CURDATE() 或 CURRENT_DATE(): 获取当前日期 (YYYY-MM-DD)

    SELECT CURDATE(); -- 输出：2023-10-27 (假设今天是2023年10月27日)

  • CURTIME() 或 CURRENT_TIME(): 获取当前时间 (HH:MM:SS)

    SELECT CURTIME(); -- 输出：14:30:00 (假设现在是下午2点30分)

  • NOW() 或 SYSDATE() 或 CURRENT_TIMESTAMP(): 获取当前日期和时间 (YYYY-MM-DD HH:MM:SS)

    SELECT NOW(); -- 输出：2023-10-27 14:30:00 (假设今天是2023年10月27日下午2点30分)

2. 提取日期时间部分

  • YEAR(date): 提取年份

    SELECT YEAR('2023-10-27'); -- 输出：2023

  • MONTH(date): 提取月份

    SELECT MONTH('2023-10-27'); -- 输出：10

  • DAY(date) 或 DAYOFMONTH(date): 提取日

    SELECT DAY('2023-10-27'); -- 输出：27

  • HOUR(time): 提取小时

    SELECT HOUR('14:30:00'); -- 输出：14

  • MINUTE(time): 提取分钟

    SELECT MINUTE('14:30:00'); -- 输出：30

  • SECOND(time): 提取秒

    SELECT SECOND('14:30:00'); -- 输出：0

  • DAYNAME(date): 提取星期几 (英文)

    SELECT DAYNAME('2023-10-27'); -- 输出：Friday

  • MONTHNAME(date): 提取月份名称 (英文)

    SELECT MONTHNAME('2023-10-27'); -- 输出：October

  • DAYOFWEEK(date): 返回星期几 (1 = 星期日, 2 = 星期一, …, 7 = 星期六)

    SELECT DAYOFWEEK('2023-10-27'); -- 输出：6 (星期五)

  • DAYOFYEAR(date): 返回一年中的第几天

    SELECT DAYOFYEAR('2023-10-27'); -- 输出：300

  • WEEK(date): 返回一年中的第几周

    SELECT WEEK('2023-10-27'); -- 输出：43

3. 日期时间计算

  • DATE_ADD(date, INTERVAL expr unit) 或 DATE_SUB(date, INTERVAL expr unit): 日期加减

    • date: 要进行计算的日期
    • INTERVAL expr unit: 时间间隔，expr 是数值，unit 是时间单位，可以是 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 等。
    -- 加一天
SELECT DATE_ADD('2023-10-27', INTERVAL 1 DAY); -- 输出：2023-10-28

-- 减一个月
SELECT DATE_SUB('2023-10-27', INTERVAL 1 MONTH); -- 输出：2023-09-27

-- 加一年两个月三天
SELECT DATE_ADD('2023-10-27', INTERVAL '1 2' YEAR_MONTH); -- 输出：2024-12-27

SELECT DATE_ADD('2023-10-27', INTERVAL '3' DAY); -- 输出：2023-10-30

  • ADDDATE(date, INTERVAL expr unit) 和 SUBDATE(date, INTERVAL expr unit)：与 DATE_ADD 和 DATE_SUB 功能相同，只是写法不同。

    -- 加一天
SELECT ADDDATE('2023-10-27', INTERVAL 1 DAY); -- 输出：2023-10-28

-- 减一个月
SELECT SUBDATE('2023-10-27', INTERVAL 1 MONTH); -- 输出：2023-09-27

  • ADDTIME(datetime, time) 和 SUBTIME(datetime, time)：在日期时间上加减时间。

    -- 加3小时
SELECT ADDTIME('2023-10-27 10:00:00', '3:0:0'); -- 输出：2023-10-27 13:00:00

-- 减30分钟
SELECT SUBTIME('2023-10-27 10:00:00', '0:30:0'); -- 输出：2023-10-27 09:30:00

  • DATEDIFF(date1, date2): 计算两个日期之间的天数差 (date1 – date2)

    SELECT DATEDIFF('2023-10-27', '2023-10-20'); -- 输出：7

  • TIMEDIFF(time1, time2): 计算两个时间之间的差 (time1 – time2)

    SELECT TIMEDIFF('14:30:00', '10:00:00'); -- 输出：04:30:00

  • LAST_DAY(date): 获取指定日期所在月份的最后一天

    SELECT LAST_DAY('2023-02-15'); -- 输出：2023-02-28

4. 日期时间格式化

  • DATE_FORMAT(date, format): 将日期时间格式化成指定的字符串

    • date: 要格式化的日期时间

    • format: 格式字符串，常用的格式符号如下：

      格式符号 描述
      %Y 4位的年份 (例如: 2023)
      %y 2位的年份 (例如: 23)
      %m 2位的月份 (01-12)
      %c 月份 (1-12)
      %d 2位的日 (01-31)
      %e 日 (1-31)
      %H 24小时制的小时 (00-23)
      %h 12小时制的小时 (01-12)
      %i 分钟 (00-59)
      %s 秒 (00-59)
      %p AM 或 PM
      %W 星期几的完整名称 (例如: Sunday)
      %a 星期几的缩写 (例如: Sun)
      %M 月份的完整名称 (例如: January)
      %b 月份的缩写 (例如: Jan) 
    SELECT DATE_FORMAT('2023-10-27 14:30:00', '%Y-%m-%d %H:%i:%s'); -- 输出：2023-10-27 14:30:00
SELECT DATE_FORMAT('2023-10-27 14:30:00', '%Y年%m月%d日 %H时%i分%s秒'); -- 输出：2023年10月27日 14时30分00秒
SELECT DATE_FORMAT('2023-10-27', '%W, %M %e, %Y'); -- 输出：Friday, October 27, 2023

  • TIME_FORMAT(time, format): 将时间格式化成指定的字符串，格式符号与 DATE_FORMAT 类似，但是只适用于时间部分。

    SELECT TIME_FORMAT('14:30:00', '%H:%i:%s'); -- 输出：14:30:00

5. 日期时间转换

  • STR_TO_DATE(str, format): 将字符串转换为日期时间类型

    • str: 要转换的字符串
    • format: 格式字符串，与 DATE_FORMAT 相同
    SELECT STR_TO_DATE('2023-10-27 14:30:00', '%Y-%m-%d %H:%i:%s'); -- 输出：2023-10-27 14:30:00 (DATETIME类型)
SELECT STR_TO_DATE('October 27, 2023', '%M %d, %Y'); -- 输出：2023-10-27 (DATE类型)

第三幕：进阶魔法 – 复杂的日期时间计算与格式化

掌握了基本魔法，咱们就可以挑战一些更高级的技巧了。

1. 获取本周、本月、本季度、本年的开始和结束日期

这在报表统计中非常有用。

-- 本周开始日期 (星期一)
SELECT DATE(SUBDATE(CURDATE(),WEEKDAY(CURDATE())));

-- 本周结束日期 (星期日)
SELECT DATE(ADDDATE(CURDATE(),6-WEEKDAY(CURDATE())));

-- 本月开始日期
SELECT DATE(MAKEDATE(YEAR(CURDATE()),1) + INTERVAL (MONTH(CURDATE())-1) MONTH);
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01'); -- 更简洁的方法

-- 本月结束日期
SELECT LAST_DAY(CURDATE());

-- 本季度开始日期
SELECT MAKEDATE(YEAR(CURDATE()),1) + INTERVAL QUARTER(CURDATE())*3-3 MONTH;

-- 本季度结束日期
SELECT LAST_DAY(MAKEDATE(YEAR(CURDATE()),1) + INTERVAL QUARTER(CURDATE())*3-1 MONTH);

-- 本年开始日期
SELECT DATE_FORMAT(CURDATE(), '%Y-01-01');

-- 本年结束日期
SELECT DATE_FORMAT(CURDATE(), '%Y-12-31');

2. 计算两个日期之间的工作日天数

这个比较复杂，需要自定义函数。

DELIMITER //

CREATE FUNCTION `WORKDAYDIFF`(start_date DATE, end_date DATE)
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE workday INT;
  DECLARE totaldays INT;
  SET workday = 0;
  SET totaldays = DATEDIFF(end_date, start_date);

  WHILE totaldays >= 0 DO
    SET @current_date = DATE_ADD(start_date, INTERVAL workday DAY);
    IF DAYOFWEEK(@current_date) BETWEEN 2 AND 6 THEN
      SET workday = workday + 1;
    END IF;
    SET totaldays = totaldays - 1;
  END WHILE;

  RETURN workday;
END//

DELIMITER ;

-- 使用示例
SELECT WORKDAYDIFF('2023-10-20', '2023-10-27'); -- 输出：5

注意： 这个函数没有考虑节假日，需要根据实际情况进行修改。

3. 根据日期时间进行分组统计

-- 按天统计订单数量
SELECT DATE(order_time), COUNT(*) FROM orders GROUP BY DATE(order_time);

-- 按月统计订单数量
SELECT DATE_FORMAT(order_time, '%Y-%m'), COUNT(*) FROM orders GROUP BY DATE_FORMAT(order_time, '%Y-%m');

-- 按小时统计订单数量
SELECT DATE_FORMAT(order_time, '%Y-%m-%d %H'), COUNT(*) FROM orders GROUP BY DATE_FORMAT(order_time, '%Y-%m-%d %H');

4. 处理时区问题

如果你的应用涉及到多个时区，你需要注意以下几点：

  • MySQL服务器的时区设置 (time_zone 系统变量)
  • 连接客户端的时区设置
  • TIMESTAMP 类型会自动进行时区转换，而 DATETIME 不会。

可以使用以下函数进行时区转换：

  • CONVERT_TZ(dt, from_tz, to_tz): 将日期时间从一个时区转换为另一个时区。

    -- 将UTC时间转换为北京时间
SELECT CONVERT_TZ('2023-10-27 10:00:00', '+00:00', '+08:00'); -- 输出：2023-10-27 18:00:00

  • @@session.time_zone: 获取当前会话的时区

    SELECT @@session.time_zone;

  • SET time_zone = '+08:00': 设置当前会话的时区

    SET time_zone = '+08:00';

第四幕：实战演练 – 几个常见的应用场景

理论学习了这么多，咱们来几个实战演练，看看这些魔法怎么用。

1. 查询最近7天的用户注册数量

SELECT DATE(register_time) AS register_date, COUNT(*) AS user_count
FROM users
WHERE register_time BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()
GROUP BY register_date
ORDER BY register_date;

2. 查询每个月销售额最高的商品

SELECT
    DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
    product_id,
    MAX(amount) AS max_amount
FROM sales
GROUP BY sale_month
ORDER BY sale_month;

3. 计算用户平均每月消费金额

SELECT
    user_id,
    AVG(monthly_amount) AS avg_monthly_amount
FROM (
    SELECT
        user_id,
        DATE_FORMAT(order_time, '%Y-%m') AS order_month,
        SUM(amount) AS monthly_amount
    FROM orders
    GROUP BY user_id, order_month
) AS monthly_sales
GROUP BY user_id;

总结：时间魔法的奥秘

好了，今天的MySQL日期时间函数之旅就到这里了。 记住，时间魔法的关键在于理解各种日期时间类型，掌握常用的日期时间函数，以及灵活运用它们来解决实际问题。 熟能生巧，多练习，你也能成为时间魔法大师！

最后，送大家一句箴言：时间就像海绵里的水，只要你肯挤，总还是有的。 当然，用MySQL的日期时间函数来管理时间，可以让你挤得更轻松！

希望今天的分享对大家有所帮助，咱们下期再见！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注