Mysql 中的日期时间函数汇总

前言

  在 MySQL 中，处理日期和时间是非常常见的需求，MySQL中内置了大量的日期和时间函数，能够灵活、方便地处理日期和时间数据，本节就简单介绍一下 MySQL中内置的日期和时间函数，以便更好地利用这些函数来处理日期和时间数据。

获取当前日期和时间

  获取当前时刻的时间就是获取程序运行的那一刻与时间相关的数据，比如年月日、时分秒等信息。

序号	函数	简要说明
1	now()	用于获取当前的日期和时间，其返回值格式为 YYYY-MM-DD HH:MM:SS。 该函数在整个查询过程中保持不变，可以确保所有记录的时间戳一致，因此被广泛用于记录数据的创建时间或更新时间。
2	curdate()	用于获取当前的日期，其返回值格式为 YYYY-MM-DD。该函数在需要仅获取日期信息时非常有用，因为它可以避免对时间部分的额外处理。
3	curtime()	用于获取当前的时间，其返回值格式为 HH:MM:SS。该函数在需要仅获取时间信息时非常有用，例如在记录事件发生的具体时间或计算时间间隔时。
4	current_timestamp()	用于获取当前的日期和时间，其返回值格式为 YYYY-MM-DD HH:MM:SS。
5	sysdate()	用于获取执行时的日期和时间。

INSERT INTO orders (order_id, order_time) VALUES (1, NOW());
SELECT COUNT(*) FROM sales WHERE sale_date = CURDATE();
SELECT CURTIME();

日期时间格式化与转换

  我们知道同一个日期时间会有多种不同的表示方式，有的时候需要在不同格式之间相互转换。在 MySQL 中，可以使用日期函数将日期和时间数据从一种格式转换为另一种格式，以下是一些常用的日期格式转换函数：

序号	函数	简要说明
1	date_format(datetime,format)	用于将日期和时间值按照指定的格式进行格式化，其返回值为一个字符串，在需要将日期和时间以特定格式展示时非常有用。 该函数支持多种格式化选项，可以根据需求灵活定制日期时间的显示格式。
2	time_format(time,format)	按照指定的格式format来格式化日期date
3	STR_TO_DATE(str, format)	用于将字符串转换为日期或日期时间值，其返回值为一个日期或日期时间值，在需要将非标准格式的字符串日期转换为标准的日期格式时非常有用。
4	FROM_DAYS()	将天数转换为日期
5	TIME_TO_SEC(time)	将time转化为秒并返回结果值，转化的公式为：小时*3600 + 分钟*60 + 秒。
6	SEC_TO_TIME(seconds)	将seconds描述转化为包含小时、分钟和秒的时间

  在上述方法中，其中 datetime 表示要被转换的具体的日期时间，format 表示要转换成的格式，可选的格式如下：

格式	描述	格式	描述	格式	描述
%Y	4位数的年	%T	返回当前的时分秒, 24-小时 (hh:mm:ss)
%b	月份对应的英文缩写	%M	月份对应的英文全称	%m	01-12的月
%c	1-12的月份数值	%d	01-31的某月里面的第几天	%e	1-31的某月里面的第几天
%D	用th后缀表示某月中的第几天	%j	001-366的一年中的第几天	%a	星期几对应的英文缩写
%W	星期几对应的英文全称	%H	00-23的小时	%h	01-12的小时
%i	00-59的分钟	%S	秒(00-59)	%f	微秒

SELECT DATE_FORMAT('2022-11-09 12:34:56', '%Y-%m-%d %H:%i:%s');

日期时间计算（加减）

  有的时候我们也需要对日期之间进行运算，比如要获取今天往前7天对应的日期，或者今天往后13天对应的日期，可以去翻日历，也可以去数数，但是这些方法肯定都不是最直接的方法，所以需要日期之间的运算。MySQL 提供了一些日期函数，可以使用这些函数来执行日期加法、日期减法等操作。以下是一些常用的日期计算函数：

序号	函数	简要说明
1	date_add(date, INTERVAL num unit)	用于在日期上添加指定的时间间隔，其返回值为一个新的日期值，常用于计算未来的日期。
2	ADDDATE(date, INTERVAL num unit)	在日期上加上指定的时间间隔。
3	ADDTIME(time1,time2)	返回time1加上time2的时间
4	date_sub(date, INTERVAL num unit)	用于从日期中减去指定的时间间隔，其返回值为一个新的日期值，常用于计算过去的日期。
5	SUBDATE(date, INTERVAL num unit)	从日期中减去指定的时间间隔。
6	SUBTIME(time1,time2)	返回time1减去time2后的时间。
7	datediff(date1,date2)	用于计算两个日期之间的天数差，其返回值为一个整数，表示两个日期之间的天数差，常用于计算时间间隔，例如任务的持续时间等。
8	TIMEDIFF(time1, time2)	计算两个时间之间的时间差
9	TIMESTAMPDIFF(unit, expr1, expr2)	计算两个日期之间的时间差，以指定的单位返回。
10	TIMESTAMPADD(unit, interval, datetime_expr)	给日期时间添加指定单位的时间
11	TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)	返回两个日期时间表达式的时间差，使用指定的单位

  在上述方法中，date表示具体的日期时间，interval是一个固定的参数，unit 表示要返回的单独的部分，unit 值可以是下列的值：

unit	说明	unit	说明	unit	说明
year	年	month	月	day	日
hour	小时	minute	分钟	second	秒
week	周数，全年第几周

SELECT DATE_ADD('2022-11-09', INTERVAL 10 DAY);
SELECT DATE_SUB('2022-11-09', INTERVAL 10 DAY);
SELECT DATEDIFF('2022-11-09', '2023-06-17');
SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-12-31');

日期时间部分提取

序号	函数	简要说明
1	YEAR(date)	用于获取日期所在的年份，其返回值为一个整数，表示年份。在实际应用中，常用于按年份对数据进行分组或筛选。
2	MONTH(date)	用于获取日期对应的月份，其返回值为一个整数，表示月份（1-12）。在实际应用中，在按月份对数据进行分组或筛选时非常有用。
3	DAY(date)	用于从日期或日期时间值中提取天数部分，其返回值为一个整数，表示月份中的天数，取值范围1-31。
4	HOUR(time)	用于从时间或日期时间值中提取小时部分，其返回值为一个整数，表示小时，取值范围0-23。
5	MINUTE(time)	用于从时间或日期时间值中提取分钟部分，其返回值为一个整数，表示分钟，取值范围0-59。
6	SECOND(time)	用于从时间或日期时间值中提取秒部分，其返回值为一个整数，表示秒，取值范围0-59。
7	DATE(date)	用于提取日期或日期时间的日期部分
8	MONTHNAME(date)	用于获取日期所在月份的英文名称。
9	DAYNAME(date)	用于获取给定日期对应星期的英文名称。
10	WEEKDAY(date)	用于获取日期对应的一周中的索引值。0表示星期一，1表示星期二，以此类推。
11	QUARTER(date)	用于获取日期对应的季度，范围为1～4。
12	WEEK(date)	用于获取给定日期所在年的第几周。
13	WEEKOFYEAR(date)	用于获取给定日期所在年份的第几周。
14	DAYOFYEAR(date)	用于获取日期是一年中的第几天
15	DAYOFMONTH(date)	用于获取日期位于所在月份的第几天
16	DAYOFWEEK(date)	用于获取日期对应的一周中的索引值。1表示星期日，2表示星期一，以此类推。
17	YEARWEEK(date)	用于获取日期所在的年份和周数。
18	LAST_DAY(date)	用于获取日期所在月份的最后一天的日期。

SELECT YEAR('2022-11-09 12:34:56');
SELECT MONTH('2022-11-09 12:34:56');
SELECT DAY('2022-11-09 12:34:56');
SELECT HOUR('2022-11-09 12:34:56');
SELECT MINUTE('2022-11-09 12:34:56');
SELECT SECOND('2022-11-09 12:34:56');

时间戳转换

序号	函数	简要说明
1	FROM_UNIXTIME(unix_timestamp[, format])	将UNIX时间戳转换为可读的日期格式。
2	UNIX_TIMESTAMP(date)	将日期转换为UNIX时间戳。

SELECT FROM_UNIXTIME(1672444800);
SELECT UNIX_TIMESTAMP(NOW());

其他有用的函数

函数	简要说明
MAKEDATE(year,n)	针对给定年份与所在年份中的天数返回一个日期。
MAKETIME(hour,minute,second)	将给定的小时、分钟和秒组合成时间并返回。

SELECT MAKEDATE(2025,1);

附录

获取本月第一天

SELECT DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE()) + 1 DAY);
SELECT CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m'), '-01');

查询今日、昨日、周、上月、本月数据

-- 今天
SELECT * FROM 表名 WHERE to_days(时间字段名) = to_days(now());

-- 昨天
SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段名) <= 1 AND TO_DAYS(NOW()) - TO_DAYS(时间字段名) > 0;

-- 近7天
SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名);

-- 查询当前这周的数据（注意：获取周是从周日至周六计算的，与我们国内周一至周日的习惯不同）
SELECT * FROM 表名 WHERE YEARWEEK(date_format(时间字段名, '%Y-%m-%d')) = YEARWEEK(now());

-- 查询上周的数据（同上）
SELECT * FROM 表名 WHERE YEARWEEK(date_format(时间字段名, '%Y-%m-%d')) = YEARWEEK(now()) - 1;

-- 近30天
SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名);

查询当前月份的数据 
-- SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名, '%Y-%m') = DATE_FORMAT(now(),'%Y-%m')；
-- SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m' )；

-- 上一月数据
SELECT * FROM 表名 WHERE PERIOD_DIFF(date_format(now( ), '%Y%m') , date_format(时间字段名, '%Y%m')) = 1;

-- 查询本季度数据
SELECT * FROM 表名 WHERE QUARTER(时间字段名) = QUARTER(now());

-- 查询上季度数据
SELECT * FROM 表名 WHERE QUARTER(时间字段名)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

-- 查询本年数据
SELECT * FROM 表名 WHERE YEAR(时间字段名)=YEAR(NOW());

-- 查询上年数据
SELECT * FROM 表名 WHERE year(时间字段名)=year(date_sub(now(),interval 1 year));

-- 查询距离当前现在6个月的数据
SELECT * FROM 表名 WHERE 时间字段名 between date_sub(now(), interval 6 month) and now();

小结

  MySQL 中的常见日期函数，包括日期函数的介绍、日期格式转换、日期计算、日期比较以及其他日期函数。这些日期函数可以帮助我们更好地处理和操作日期和时间数据，提高数据库的查询和分析效率。熟练掌握这些日期函数，将有助于我们在 MySQL 中更好地处理日期和时间相关的业务需求。