MYSQL知识点整理-日期函数

一. 获取时间

-- 获取当前时间\时间戳
SELECT NOW();
SELECT CURRENT_DATE, CURRENT_DATE();
SELECT CURRENT_TIME, CURRENT_TIME();
SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP();

SELECT CURDATE();
SELECT CURTIME();
SELECT LOCALTIME, LOCALTIME();
SELECT LOCALTIMESTAMP, LOCALTIMESTAMP();

SELECT SYSDATE();
SELECT UTC_TIME, UTC_TIME();
SELECT UTC_DATE, UTC_DATE();
SELECT UTC_TIMESTAMP, UTC_TIMESTAMP();
SELECT UNIX_TIMESTAMP();

二. 获取时间的属性

-- 获取时间的属性
SELECT YEAR(CURRENT_DATE);
SELECT QUARTER(CURRENT_DATE);
SELECT MONTH(CURRENT_DATE);
SELECT DAY(CURRENT_DATE);
SELECT HOUR(CURRENT_TIME);
SELECT MINUTE(CURRENT_TIME);
SELECT SECOND(CURRENT_TIME);
SELECT TIMESTAMP(CURRENT_TIME);

SELECT DAYNAME(CURRENT_DATE); -- 英文星期几
SELECT MONTHNAME(CURRENT_DATE); -- 月份的英文
SELECT DAYOFMONTH(CURRENT_DATE); -- 本月第几天
SELECT DAYOFWEEK(CURRENT_DATE); -- 返回星期几，星期日是1,以此类推
SELECT DAYOFYEAR(CURRENT_DATE); -- 日期所在年份的第几天
SELECT LAST_DAY(CURDATE()); -- 本月最后一天

-- 从日期 d 中获取指定的值，type 指定返回的值。格式为EXTRACT(type of day)，type支持多种类型，如:
-- 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
SELECT EXTRACT(DAY FROM CURRENT_DATE);
SELECT EXTRACT(DAY FROM '2024-02-01');

　　

三. 时间加减计算

-- 减少 DATE_SUB(date,INTERVAL expr unit)
SELECT DATE_SUB(CURTIME(), INTERVAL 1 MICROSECOND); -- 减毫秒
SELECT DATE_SUB(CURTIME(), INTERVAL 1 SECOND); -- 秒
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MINUTE); -- 分钟
SELECT DATE_SUB(CURDATE(), INTERVAL 1 HOUR); -- 小时
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY); -- 天
SELECT DATE_SUB(CURDATE(), INTERVAL 1 WEEK); -- 周
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH); -- 月
SELECT DATE_SUB(CURDATE(), INTERVAL 1 QUARTER); -- 季
SELECT DATE_SUB(CURDATE(), INTERVAL 1 YEAR); -- 年
SELECT DATE_SUB(CURRENT_TIME, INTERVAL 1 DAY); -- 时间减少
-- 减少 SUBDATE(date,INTERVAL expr unit)
SELECT SUBDATE(CURDATE(), INTERVAL 1 DAY);


-- 增加 DATE_ADD(date,INTERVAL expr unit)
SELECT DATE_ADD(CURTIME(), INTERVAL 1 MICROSECOND);
-- 增加 ADDDATE(date,INTERVAL expr unit)
SELECT ADDDATE(CURDATE(), INTERVAL 1 DAY);
-- 增加指定单位 TIMESTAMPADD(unit,interval,datetime_expr)
SELECT TIMESTAMPADD(DAY,1,'2024-01-01');

-- 差值计算
SELECT DATEDIFF('2024-01-01', '2024-02-01');
-- 时间差值 TIMEDIFF(expr1,expr2)
SELECT TIMEDIFF('2024-01-01 12:00:00', '2024-01-01 10:00:00');
-- 求时间差值 SUBTIME(expr1,expr2)
SELECT SUBTIME('12:00:00', '10:00:00');
-- 求指定单位的差值 TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
SELECT TIMESTAMPDIFF(DAY,'2024-01-01','2024-01-03');


-- 添加月份 PERIOD_ADD(P,N)
SELECT PERIOD_ADD(202401, 1);
-- 添加天数
SELECT PERIOD_ADD(20240101, 1);
-- 求天数差值 PERIOD_DIFF(P1,P2)
SELECT PERIOD_DIFF(20240102, 20240101);
-- 求月份差值
SELECT PERIOD_DIFF(202401, 202310);

四. 格式化

-- 格式化 DATE_FORMAT(date,format)
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
-- 字符串转日期 STR_TO_DATE(str,format)
SELECT STR_TO_DATE('2024-08-04 11:00:00', '%Y-%m-%d %H:%i:%S');
-- 获取时间格式，GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})
SELECT GET_FORMAT(DATE, 'ISO');
SELECT GET_FORMAT(TIME, 'ISO');
SELECT GET_FORMAT(TIMESTAMP, 'ISO');

五. 转换函数

-- 时间转秒数 TIME_TO_SEC(time)
SELECT TIME_TO_SEC('10:00:00');
-- 秒数转时间 SEC_TO_TIME(seconds)
SELECT SEC_TO_TIME(3600); 

-- 时间转天数 TO_DAYS(date)
SELECT TO_DAYS('2024-01-01'); 
SELECT TO_DAYS('0000-00-00');

-- 使用天数创建日期 FROM_DAYS(N)
SELECT FROM_DAYS(720000);
SELECT FROM_DAYS(0);

-- 拼凑日期 MAKEDATE(year,dayofyear)
SELECT MAKEDATE(2024, 1);
-- 拼凑时间 MAKETIME(hour,minute,second)
SELECT MAKETIME(1,1,1);


-- 转换时区 CONVERT_TZ(dt,from_tz,to_tz)
SELECT CONVERT_TZ('2024-01-01 00:00:00','+08:00','+00:00');

-- 求指定日期的UNIX时间戳 
SELECT UNIX_TIMESTAMP('2024-01-01');


-- 使用时间戳创建时间 FROM_UNIXTIME(unix_timestamp)
SELECT FROM_UNIXTIME(1704038400);
-- 使用时间戳按指定格式创建时间 FROM_UNIXTIME(unix_timestamp,format)
SELECT FROM_UNIXTIME(1704038400,'%Y-%m-%d');