目录
1,date_add(datetime,interval num type)
2,date_sub(datetime,interval num type)
11,maketime(hour,minute,second)
一,日期与时间基础函数
- curdate()--只返回当前日期----年-月-日
- curtime()--只返回当前时间----时:分:秒
- now()--返回当前系统的日期和时间----年-月-日 时:分:秒
- utc_date()--返回世界标准时间的日期----年-月-日
- utc_time()--返回世界标准时间的时间----时:分:秒
select curdate(),curtime(),now(),utc_date(),utc_time();
二,日期与时间戳的转换
为了更加直观以下的日期均采用全称输入,可以直接使用now()函数获取系统时间
- unix_timestamp()--以unix时间戳的形式返回当前的日期
- unix_timestamp(date)--将date时间以unix时间戳的形式返回
- from_unixtime(timestamp)--将unix时间戳的时间转换成普通格式的时间
【unix时间戳是从1970年1月1日0时0分0秒开始计算秒数的一个数值】
select unix_timestamp(),unix_timestamp('2022-08-26 10:29:01'),from_unixtime(1661483005);
三,获取日期里面的月份,星期,星期数,天数
1,分被获取输入的date日期的年,月,日
- year(date):获取年
- month(date):获取月
- day(day):获取日
select year('2022-08-26 10:29:01'),month('2022-08-26 10:29:01'),day('2022-08-26 10:29:01');
2,分别获取获取输入的date日期的时,分,秒
- hour(date):获取date时间里面的时
- minute(date):获取date时间里面的分
- second(date):获取到date时间里面的秒
3,获取月份名及星期名
- monthname(date):获取月份名(英文表示)
- dayname(date):获取星期名(英文表示)
4,获取该日期对应的季度
1,quarter(date):可以获取到我们输入的日期对应的季度
【quarter有:把........四等分的意思。
传统上我们习惯性的就是第一季度从1到3月份,第二季度从4到6月份,第三季度从7到9月份,第四季度从10到12月份,如果学过地理的可能会不大习惯,因为如果严格按照中国维度划分的话,3月20号春分,预示着3到5月份为第一季度。以此类推。不过我们也说了“如果按照中国的维度划分”,而我们都知道mysql是瑞典人开发的,不看我们中国的维度,因此季度的划分就是按照:1到3月份为第一季度,并以此类推下来的。】
如上,我们的8月份就是第三季度。
5,获取日期是一周中的第几天
我们可以使用两个函数来返回我们输入的date日期是一周中的第几天,不过我比较喜欢dayofweek()这个函数,因为它的一周就是按照西方的周日就是一周的开始(因此表示为1)标准来的,而weekday函数的周一却是使用0来进行表示。
- weekday(date):返回该日期位于周几---0表示周一......6表示周日
- dayofweek(date):返回该日期位于周几--1表示周日.....7表示周六
在日期为:2022-08-26时,为周五,因此weekday函数返回的数字为4(5-1),dayofweek返回6(5+1);
6,获取该日期是一年或一月中的第几天,第几周
- weekofyear(date)--返回该date日期在一年中是第几周
- dayofyear(date)--返回该date日期在一年中是第几天
- dayofmonth(date)--返回该date日期位于所在月份的第几天
四,时间和秒钟转换
1,time_to_sec(time)
该函数我们可以翻译为:time时间to转换到second秒钟,因此我们需要往该函数传入的参数为时间:
2,sec_to_time(sec)
该函数的使用与time_to_sec(time)函数对应,是将秒数转换成时间,如下:
五,计算日期和时间
1,date_add(datetime,interval num type)
该函数看名字就知道是关于日期加减的,函数里面的参数:
- datetime:要进行加减的日期
- interval:翻译为间隔,是个关键字。
- num:正数表示在日期的基础上加,负数表示减
- type:表示要加或减的是年,月,日,时,分,还是秒。如果是对时做修改,那么type需要写入hour。
mysql> select date_add(now(),interval 1 year); #在当前日期的基础上再加一年
+---------------------------------+
| date_add(now(),interval 1 year) |
+---------------------------------+
| 2023-09-11 21:32:43 |
+---------------------------------+
1 row in set (1.73 sec)
mysql> select date_add(now(),interval 1 month);
+----------------------------------+
| date_add(now(),interval 1 month) |
+----------------------------------+
| 2022-10-11 21:32:53 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select date_add(now(),interval 1 day);
+--------------------------------+
| date_add(now(),interval 1 day) |
+--------------------------------+
| 2022-09-12 21:33:01 |
+--------------------------------+
1 row in set (0.29 sec)
mysql> select date_add(now(),interval -1 day); #在当前日期的基础上减一年
+---------------------------------+
| date_add(now(),interval -1 day) |
+---------------------------------+
| 2022-09-10 21:37:31 |
+---------------------------------+
1 row in set (1.75 sec)
type也可以是两个日期加或减,如:
- 年月:year_month
- 日时:day_hour
- 日分:day_minute
- 日秒:day_second
- 时分:hour_minute
- 时秒:hour_second
- 分秒:minute_second
mysql> select date_add(now(),interval 1 year_month); #年份和月份同时加1
+---------------------------------------+
| date_add(now(),interval 1 year_month) |
+---------------------------------------+
| 2022-10-11 23:09:24 |
+---------------------------------------+
1 row in set (0.04 sec)
mysql> select date_add(now(),interval 1 day_hour); #天数和小时同时加1
+-------------------------------------+
| date_add(now(),interval 1 day_hour) |
+-------------------------------------+
| 2022-09-12 00:16:47 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add(now(),interval 1 minute_second); #分钟和秒数同时加1
+-------------------------------------------+
| date_add(now(),interval 1 minute_second) |
+-------------------------------------------+
| 2022-09-11 23:17:02 |
+-------------------------------------------+
1 row in set (0.03 sec)
中间的num的值也不是只能写一个,我们可以分别指定增加的数,如下,我们如果先要年加2,月加5:
mysql> select now(), date_add(now(),interval '2_5' year_month);
看到这里大家可能会有疑惑,年那里明明写的是加2,按理来说应该是2024的结果,能够有这个问题的表示还是有认真的看的,这个问题的原因是我们的月份进1了,因为此时我们的月份为9月份,然后我们又让月份加4,变成了来年的1月份,我们的年份自然就要再加1。
2,date_sub(datetime,interval num type)
该函数与date_add函数里面传入的num为负数时的效果一样,都是表示在当前日期的基础上减去相应的日期。如下:
所以我们可以直接使用date_add函数即可。
3,addtime(time1,time2)
该函数返回的是time1加上time2的时间如果time2为一个数字时,代表的时秒,可以为负数。
mysql> select now(),addtime(now(),-22);
4,subtime(time1,tim2)
返回time1减去time2后的时间,time2可以是数字代笔秒数,也可以是负数(正负数都一样的结果)。
5,datediff(date1,date2)
diff可以理解为different的缩写,即不同的意思。
该函数返回date1到date2之间间隔的天数:
mysql> select datediff(now(),date_add(now(),interval '1_1' year_month));
6,timediff(time1,time2)
timediff函数的使用与datediff差不多,只是timediff返回的是时间间隔:
mysql> select timediff(now(),date_add(now(),interval '1_1' year_month));
7,time_days(n)
该函数返回从0000年1月1日起,n天后的日期【这里需要注意的就是当我们的n小于366时,返回的结果为0年0月0日(具体为啥我也不是很能够理解,如果知道原因的请在评论区回复,谢谢!)】
8,to_days(date)
返回日期date距离0年1月1日的天数
9,last_day(date)
返回date所在月份的最后一天的日期
10,makedate(year,n)
针对给定年份与所在年份中的天数返回一个日期
11,maketime(hour,minute,second)
将给定的时分秒组合成时间并返回
12,period_add(time,n)
返回time加上n后的时间。
如我们现在的时间为2022年09月15日19点42分07秒,如果我们想要使用这个函数的话就需要将日期转换成数值类型(日期+0),或者是直接写上日期:20220915194207,如下我们使用该函数,并将我们的日期传进去,第二个参数写5,代表我们的日期那里加上5秒:
有趣的是,在mysql5和mysql8中该函数的使用效果是不一样的:
如上我们看到在mysql8中我们可以使用该方式传入我们的年月日时分秒,返回的就是秒数加n。
而我们使用mysql5的话返回的数字确实不一样的,只能说mysql5和mysql8各有利弊了。
六,日期格式转换
有些时候使用mysql显示出来的日期格式并不符合我们自己的需求,这个时候我们就想要对日期进行相应的日期格式化【格式化是将日期格式化成字符串】或解析【将字符串解析成日期】。
如下为我自己的记忆方式,仅供参考:
格式化有两函数可分别格式化年月日date_format(date,format),时分秒time_format(time,format)。
解析有一函数,将字符串解析成一个日期str_to_date(string,format);
还有一函数可返回日期字符串的显示格式get_format(date_type,format_type);
mysql中的日期格式符:
年有大小y--大四小两。,大Y为四,小y为两(数来表示);
月有大小m--大全小两b缩c一。大M月全称,小m为两;还有b和c,b为英文缩写,c为一;
日有大小d--大第小两e一,大D第,小d两,小e一;
时有大小h--大24小12,皆为两,大H为24时,h为12时;
分有i,为两;
秒有s,为两。
接下来让我们使用命令来显示具体的使用效果:
1显示“2022###September###15th”
mysql> select date_format(curdate(),'%Y#%M#%D');
2显示“2022/09/15”
mysql> select date_format(curdate(),'%Y/%m/%d');
3显示“22:56”
mysql> select time_format(curtime(),'%H:%i');
4显示“2023-9-15”
mysql> select str_to_date('2023年9月15号','%Y年%m月%d');