获取日期、时间

函数 用法
CURDATE()，CURRENT_DATE() 返回当前日期，只包含年、月、日
CURTIME()，CURRENT_TIME() 返回当前时间，只包含时、分、秒
NOW()，SYSDATE()，CURRENT_TIMESTAMP()，LOCALTIME()，LOCALTIMESTAMP() 返回当前系统日期和时间
UTC_DATE() 返回 UTC（世界标准时间）日期
UTC_TIME() 返回 UTC（世界标准时间）时间 
  1. SELECT 
  2.     CURDATE(),
  3.     CURTIME(),
  4.     NOW(),
  5.     SYSDATE()+0,
  6.     UTC_DATE(),
  7.     UTC_DATE()+0,
  8.     UTC_TIME(),
  9.     UTC_TIME()+0
  10. FROM DUAL;

日期与时间戳的转换

函数 用法
UNIX_TIMESTAMP() 以 UNIX 时间戳的形式返回当前时间。
UNIX_TIMESTAMP(date) 将时间 date 以 UNIX 时间戳的形式返回。
FROM_UNIXTIME(timestamp) 将 UNIX 时间戳的时间转换为普通格式的时间 
  1. SELECT UNIX_TIMESTAMP(now());
  2. SELECT UNIX_TIMESTAMP(CURDATE());
  3. SELECT UNIX_TIMESTAMP(CURTIME());
  4. SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11');

获取月份、星期、星期数、天数等函数

函数 用法
YEAR(date) / MONTH(date) / DAY(date) 返回具体的日期值
HOUR(time) / MINUTE(time) / SECOND(time) 返回具体的时间值
MONTHNAME(date) 返回月份：January，…
DAYNAME(date) 返回星期几：MONDAY，TUESDAY … SUNDAY
WEEKDAY(date) 返回周几，注意，周 1 是 0，周 2是 1，… 周日是 6
QUARTER(date) 返回日期对应的季度，范围为1～4
WEEK(date) ， WEEKOFYEAR(date) 返回一年中的第几周
DAYOFYEAR(date) 返回日期是一年中的第几天
DAYOFMONTH(date) 返回日期位于所在月份的第几天
DAYOFWEEK(date) 返回周几，注意：周日是 1，周一是 2，… 周六是 7 
  1. SELECT 
  2.     YEAR(CURDATE()),
  3.     MONTH(CURDATE()),
  4.     DAY(CURDATE()),
  5.     HOUR(CURTIME()),
  6.     MINUTE(NOW()),
  7.     SECOND(SYSDATE())
  8. FROM DUAL;
  1. SELECT 
  2.     MONTHNAME('2021-10-26'),
  3.     DAYNAME('2021-10-26'),
  4.     WEEKDAY('2021-10-26'),
  5.     QUARTER(CURDATE()),
  6.     WEEK(CURDATE()),
  7.     DAYOFYEAR(NOW()),
  8.     DAYOFMONTH(NOW()),
  9.     DAYOFWEEK(NOW())
  10. FROM DUAL;

日期的操作函数

函数 用法
EXTRACT(type FROM date) 返回指定日期中特定的部分，type 指定返回的值

EXTRACT(type FROM date) 函数中 type 的取值与含义：
image-20211012142639469.png
image-20211012142746444.png

  1. SELECT 
  2.     EXTRACT(MINUTE FROM NOW()),
  3.     EXTRACT( WEEK FROM NOW()),
  4.     EXTRACT( QUARTER FROM NOW()),
  5.     EXTRACT( MINUTE_SECOND FROM NOW())
  6. FROM DUAL;

时间和秒钟转换的函数

函数 用法
TIME_TO_SEC(time) 将 time 转化为秒并返回结果值。转化的公式为：小时3600+分钟60+秒
SEC_TO_TIME(seconds) 将 seconds 描述转化为包含小时、分钟和秒的时间 
  1. SELECT TIME_TO_SEC(NOW());
  2. SELECT SEC_TO_TIME(78774);

计算日期和时间的函数

函数 用法
DATE_ADD(datetime, INTERVAL expr type)，
ADDDATE(date, INTERVAL expr type)		 返回与给定日期时间相差 INTERVAL 时间段的日期时间
DATE_SUB(date, INTERVAL expr type)，
SUBDATE(date, INTERVAL expr type)		 返回与 date 相差 INTERVAL 时间间隔的日期

上述函数中 type 的取值：
image-20211012143203355.png

  1. SELECT
  2.     DATE_ADD( NOW(), INTERVAL 1 DAY ) AS col1,
  3.     DATE_ADD( '2021-10-21 23:32:12', INTERVAL 1 SECOND ) AS col2,
  4.     ADDDATE( '2021-10-21 23:32:12', INTERVAL 1 SECOND ) AS col3,
  5.     DATE_ADD( '2021-10-21 23:32:12', INTERVAL '1_1' MINUTE_SECOND ) AS col4,
  6.     DATE_ADD( NOW(), INTERVAL - 1 YEAR ) AS col5,# 可以是负数
  7.     DATE_ADD( NOW(), INTERVAL '1_1' YEAR_MONTH ) AS col6 # 需要单引号
  8. FROM DUAL;
函数 用法
ADDTIME(time1,time2) 返回 time1 加上 time2 的时间。当 time2 为一个数字时，代表的是秒，可以为负数
SUBTIME(time1,time2) 返回 time1 减去 time2 后的时间。当 time2 为一个数字时，代表的是秒，可以为负数
DATEDIFF(date1,date2) 返回 date1 - date2 的日期间隔天数
TIMEDIFF(time1, time2) 返回 time1 - time2 的时间间隔
FROM_DAYS(N) 返回从 0000 年 1 月 1 日起，N 天以后的日期
TO_DAYS(date) 返回日期 date 距离 0000 年 1 月 1 日的天数
LAST_DAY(date) 返回 date 所在月份的最后一天的日期
MAKEDATE(year,n) 针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second) 将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n) 返回 time 加上 n 后的时间 
  1. SELECT
  2.     ADDTIME( NOW(), 20 ),
  3.     SUBTIME( NOW(), 30 ),
  4.     SUBTIME( NOW(), '1:1:3' ),
  5.     DATEDIFF( NOW(), '2021-10-01' ),
  6.     TIMEDIFF( NOW(), '2021-10-25 22:10:10' ),
  7.     FROM_DAYS( 366 ),
  8.     TO_DAYS( '0000-12-25' ),
  9.     LAST_DAY( NOW()),
  10.     MAKEDATE( YEAR ( NOW()), 12 ),
  11.     MAKETIME( 10, 21, 23 ),
  12.     PERIOD_ADD( 20200101010101, 10 ) 
  13. FROM DUAL;

举例：查询 7 天内的新增用户数有多少？

  1. SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

日期的格式化与解析

函数 用法
DATE_FORMAT(date,fmt) 按照字符串 fmt 格式化日期 date 值
TIME_FORMAT(time,fmt) 按照字符串 fmt 格式化时间 time 值
GET_FORMAT(date_type,format_type) 返回日期字符串的显示格式
STR_TO_DATE(str, fmt) 按照字符串 fmt 对 str 进行解析，解析为一个日期

上述非 GET_FORMAT 函数中 fmt 参数常用的格式符：

格式符 说明
%Y 4 位数字表示年份
%M 月名表示月份（January，….）
%b 缩写的月名（Jan.，Feb.，….）
%D 英文后缀表示月中的天数（1st，2nd，3rd，…）
%e 数字形式表示月中的天数（1，2，3，…）
%H 两位数字表示小数，24 小时制（01，02，…）
%k 数字形式的小时，24 小时制(1，2，3，…)
%i 两位数字表示分钟（00，01，02，…）
%W 一周中的星期名称（Sunday…）
%w 以数字表示周中的天数（0=Sunday，1=Monday，…）
%j 以 3 位数字表示年中的天数（001，002，…）
%u 以数字表示年中的第几周，（1，2，3，…）其中 Monday 为周中第一天
%T 24 小时制
%p AM 或 PM
%y 表示两位数字表示年份
%m 两位数字表示月份（01，02，03，…）
%c 数字表示月份（1，2，3，…）
%d 两位数字表示月中的天数（01，02，..）
%h 和 %I 两位数字表示小时，12 小时制（01，02，…）
%l 数字形式表示小时，12 小时制（1，2，3，4，…）
%S 和 %s 两位数字表示秒（00，01，02，…）
%a 一周中的星期缩写（Sun.，Mon.，Tues.，…）
%U 以数字表示年中的第几周，（1，2，3，…）其中 Sunday 为周中第一天
%r 12 小时制
%% 表示 %

GET_FORMAT 函数中 date_type 和 format_type 参数取值如下：
image-20211012145231321.png
举例：

  1. SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
  2. SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y');
  3. SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s');
  4. SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s');
  5. SELECT GET_FORMAT(DATE, 'USA');
  6. SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA'));
  7. SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d');