MySQL-日期时间函数详解及练习

目录

3.1 返回当前日期

3.2 提取日期部分

3.3 增加或减去时间

3.4 格式化时期或时间

3.5 牛客练习题

---

3.1 返回当前日期

1. CURDATE() 或 CURRENT_DATE() | 返回当前日期

select curdate();

select current_date();

结果：

2. CURTIME() 或 CURRENT_TIME() | 返回当前时间

select curtime();

select current_time();

结果：

3. NOW()或CURRENT_TIMESTAMP()或LOCALTIME()或LOCALTIMESTAMP() | 返回当前系统日期时间

select now();
select current_timestamp();
select localtimestamp();

结果：

4. SYSDATE() | 返回函数执行时的时间

注意：NOW()取自mysql的变量”TIMESTAMP”，而这个变量在语句开始执行的时候就设定好了，所以在整个语句执行过程当中都不会变化，而SYSDATE()取的是动态的实时时间。

# 这里两个now返回的值是一样的，因为都是表示语句开始执行的时间
select now(),sleep(3),now();

# SYSDATE获取当时执行时实时的时间
select sysdate(),sleep(3),sysdate();

结果：

也正因为有这个区别，我们一般在执行语句的时候，都是用NOW()，因为SYSDATE获取当时实时的时间，这有可能导致主库和从库是执行的返回值是不一样的，导致主从数据不一致。

3.2 提取日期部分

1.【YEAR(date) | 提取年份】、【MONTH(date) | 提取月份】、【DAY(date) | 提取天数】、【HOUR(time) | 提取小时数】、【MINUTE(time) | 提取分钟】、【SECOND(time) | 提取秒】

select year ('2023-12-7'),month('2023-12-7'),day('2023-12-7');
select hour('2023-12-7 20:48:23'),minute('2023-12-7 20:48:23'),second('2023-12-7 20:48:23');

结果：

2.【DAYOFYEAR(date) | 返回一年中的天数（1-366）】、【DAYOFMONTH(date) | 同DAY(date)，返回月份中的天数】、【DAYOFWEEK(date) | 返回周几的索引，注意：周日是1，周一是2，。。。周六是7】

select dayofyear ('2023-12-7'),dayofmonth ('2023-12-7'),dayofweek('2023-12-7');

结果：

3.【WEEK(date)或WEEKOFYEAR(date) | 返回一年中的第几周】、【YEARWEEK(date) | 返回年份和周数】

select week ('2023-12-7'),weekofyear('2023-12-7'),yearweek('2023-12-7');

结果：

4.【MONTHNAME(date) | 返回月份的名称】、【DAYNAME(date) | 返回给定日期对应的周几的名称】

select monthname('2023-12-7'),dayname('2023-12-7');

结果：

3.3 增加或减去时间

1. DATE_ADD(date, INTERVAL expr unit) | 向日期值增加时间间隔

#数值可以是负数，unit可以是如果表达式列出的任意一个类型
select date_add('2023-12-7 20:48:23',interval 1 year) as 'add 1 year',
       date_add('2023-12-7 20:48:23',interval '1_2' YEAR_MONTH) as 'add 1 year and 2 month',
       date_add('2023-12-7 20:48:23',interval -1 day) as 'sub 1 day';
       
select date_add('2023-12-7 20:48:23',interval 1 hour) as 'add 1 hour',
       date_add('2023-12-7 20:48:23',interval '1_2' HOUR_MINUTE) as 'add 1 hour and 2 mins',
       date_add('2023-12-7 20:48:23',interval -1 second ) as 'sub 1 second';

结果：

unit 表达式类型	YEAR_MONTH
YEAR	DAY_HOUR
MONTH	DAY_MINUTE
DAY	DAY_SECOND
HOUR	HOUR_MINUTE
MINUTE	HOUR_SECOND
SECOND	MINUTE_SECOND

2.【DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔】、【TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔】

select datediff('2023-12-7','2023-12-15') as '日期差',
       timediff('2023-12-7 20:48:23','2023-12-7 19:38:00') as '时间差';

结果：

3.4 格式化时期或时间

1. DATE_FORMAT(date, format) | 按指定格式格式化日期

select date_format('2023-12-7 20:48:23','%Y/%m/%d'),
       date_format('2023-12-7 20:48:23','%Y-%m-%d');

结果：

2. STR_TO_DATE(str,format) | 将时间格式的字符串（str），按照所提供的显示格式（format）转换为DATETIME类型的值

#STR_TO_DATE()函数可能会根据输入和格式字符串返回DATE，TIME或DATETIME值。
select str_to_date('2023-12-7 20:48:23','%Y/%m/%d'),
       str_to_date('2023-12-7 20:48:23','%Y-%m-%d'),
       str_to_date('2023-12-7 20:48:23','%Y-%m-%d %H:%i:%s');

结果：

• #如果输入字符串是非法的，则STR_TO_DATE()函数返回NULL。

• #如果月份和日期的数字小于10，用0填充，显示两位数字；

3. 常见的format格式

DATE_FORMAT字符串格式	格式化日期
%Y/%m/%d	2022/04/26
%Y-%m-%d	2022-04-26
%e/%c/%Y	4/8/2022
%d/%m/%Y %H:%i	26/04/2022 17:27
%b %d %Y %h:%i %p	Apr 26 2022 05:28 PM
%W %D %M %Y %T	Tuesday 26th April 2022 17:31:34

4. 格式符详解：

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

3.5 牛客练习题

牛客SQL28-计算用户8月每天的练题数量