MySQL常用日期函数与日期转换格式化函数整理

左手的ㄟ右手 2023-08-17 15:29 170阅读 0赞

### MySQL常用日期函数与日期转换格式化函数整理 ###

*   *  返回星期几：DAYOFWEEK
     *  返回date是在一周中的序号:WEEKDAY
     *  返回date是当月的第几天：DAYOFMONTH
     *  返回date是当年的第几天：DAYOFYEAR
     *  返回date是当年的第几月：MONTH
     *  返回date是周几的英文全称名字：DAYNAME
     *  返回date是第几月的英文名字：MONTHNAME
     *  返回date是第几个季度：QUARTER
     *  返回date在一年当中的第几周：WEEK
     *  返回date所在年份：WEEK
     *  返回该date或者time的hour值值范围（0-23）
     *  返回该time的minute值，值范围（0-59）
     *  返回该time的second值，值范围（0-59）
     *  返回对month做增减的操作结果
     *  该函数返回monthStart - monthEnd的间隔月数
     *  DATE\_ADD()和ADDDATE()返回对date操作的结果
     *  DATE\_ADD(date，INTERVAL number type)，同 ADDDATE()

## 返回星期几：DAYOFWEEK ##

SELECT DAYOFWEEK('2020-10-28')
    SELECT DAYOFWEEK(now()) //now为当前日期

![在这里插入图片描述][20201028190211204.png_pic_center]

> 5 (表示返回日期date是星期几，记住：星期天=1，星期一=2，星期二=3，星期三=4，星期四=5，星期五=6， 星期六=7)

## 返回date是在一周中的序号:WEEKDAY ##

SELECT WEEKDAY('2020-10-28')

![在这里插入图片描述][20201028190404221.png_pic_center]

> 6(表示返回date是在一周中的序号，西方日历中通常一周的开始是星期天，并且以0开始计数，所以，记住：0=星期一，1=星期二， … 5=星期六,6=星期天)

## 返回date是当月的第几天：DAYOFMONTH ##

SELECT DAYOFMONTH('2020-10-28')

![在这里插入图片描述][20201028190426570.png_pic_center]

> 28 (表示返回date是当月的第几天，1号就返回1，… ,31号就返回31)

## 返回date是当年的第几天：DAYOFYEAR ##

SELECT DAYOFMONTH('2019-09-22')

> 265 (表示返回date是当年的第几天，01.01返回1，… ,12.31就返回365)

## 返回date是当年的第几月：MONTH ##

SELECT MONTH('2019-09-22')

> 9 (表示返回date是当年的第几月，1月就返回1，… ,12月就返回12)

## 返回date是周几的英文全称名字：DAYNAME ##

SELECT DAYNAME('2019-09-22')

> Sunday(表示返回date是周几的英文全称名字)

## 返回date是第几月的英文名字：MONTHNAME ##

SELECT MONTHNAME('2019-09-22')

> September(表示返回date的是当年第几月的英文名字)

## 返回date是第几个季度：QUARTER ##

SELECT QUARTER('2019-09-22')

> 3 (表示返回date的是当年的第几个季度，返回1,2,3,4)

## 返回date在一年当中的第几周：WEEK ##

SELECT WEEK('2019-09-22')

> 38 得到38，9月22日是2019年的第38个周  
> 1 (该函数返回date在一年当中的第几周，date(01.03)是周日，默认是以为周日作为一周的第一天，函数在此处返回1可以有两种理解：1、第一周返回0，第二周返回1，… ,2、以当年的完整周开始计数，第一周返回1，第二周返回2，… ，最后一周返回53)  
> 1 (week()默认index就是0. 所以结果同上)  
> 0 (当index为1时，表示一周的第一天是周一，所以，4号周一才是第二周的开始日)

## 返回date所在年份：WEEK ##

SELECT  YEAR('80-09-22')
    SELECT YEAR('2019-09-22')
    SELECT YEAR('2019-09-22 00:00:00')

> 1980 (表示返回date的4位数年份)  
> 2019  
> 2019

注意：

> 如果年份只有两位数，那么自动补全的机制是以默认时间1970.01.01为界限的，>= 70 的补全 19，< 70 的补全 20

## 返回该date或者time的hour值值范围（0-23） ##

SELECT HOUR('11:22:33');
    SELECT HOUR('2020-05-13 11:22:33')

![在这里插入图片描述][20200513165219852.png]  
![在这里插入图片描述][20200513165211581.png]

## 返回该time的minute值，值范围（0-59） ##

SELECT MINUTE('11:22:33')  
    SELECT MINUTE('2016-01-16 11:44:33')

> 22  
> 44

## 返回该time的second值，值范围（0-59） ##

SELECT SECOND('11:22:33')  
    SELECT SECOND('2016-01-16 11:44:22')

> 33  
> 22

## 返回对month做增减的操作结果 ##

month的格式为yyMM或者yyyyMM,返回的都是yyyyMM格式的结果，add可以传负值

SELECT PERIOD_ADD(1601,2) 
    SELECT PERIOD_ADD(191602,3) 
    SELECT PERIOD_ADD(191602,-3)

> 201603  
> 191605  
> 191511

## 该函数返回monthStart - monthEnd的间隔月数 ##

该函数返回monthStart - monthEnd的间隔月数

SELECT PERIOD_DIFF(1601,1603) 
    SELECT PERIOD_DIFF(191602,191607) 
    SELECT PERIOD_DIFF(1916-02,1916-07) 
    SELECT PERIOD_DIFF(1602,9002)

> \-2  
> \-5  
> 5  
> 312

## DATE\_ADD()和ADDDATE()返回对date操作的结果 ##

SELECT DATE_ADD(“2015-12-31 23:59:59”,INTERVAL 1 SECOND) 
    SELECT DATE_ADD(“2015-12-31 23:59:59”,INTERVAL 1 DAY) 
    SELECT DATE_ADD(“2015-12-31 23:59:59”,INTERVAL “1:1” MINUTE_SECOND) 
    SELECT DATE_ADD(“2016-01-01 00:00:00”,INTERVAL “-1 10” DAY_HOUR)

> 2016-01-01 00:00:00  
> 2016-01-01 23:59:59  
> 2016-01-01 00:01:00  
> 2015-12-30 14:00:00

## DATE\_ADD(date，INTERVAL number type)，同 ADDDATE() ##

1、date的格式可以是“15-12-31”，可以是“15-12-31 23:59:59”,也可以是“2015-12-31 23:59:59”，如果参数date是date格式，则返回date格式结果，如果参数date是datetime格式，则返回datetime格式结果

2、type格式：

SECOND 秒 SECONDS
    MINUTE 分钟 MINUTES
    HOUR 时间 HOURS
    DAY 天 DAYS
    MONTH 月 MONTHS
    YEAR 年 YEARS
    MINUTE_SECOND 分钟和秒 "MINUTES:SECONDS"
    HOUR_MINUTE 小时和分钟 "HOURS:MINUTES"
    DAY_HOUR 天和小时 "DAYS HOURS"
    YEAR_MONTH 年和月 "YEARS-MONTHS"
    HOUR_SECOND 小时, 分钟， "HOURS:MINUTES:SECONDS"
    DAY_MINUTE 天, 小时, 分钟 "DAYS HOURS:MINUTES"
    DAY_SECOND 天, 小时, 分钟, 秒 "DAYS HOURS:MINUTES:SECONDS"

3、另外，如果不用函数，也可以考虑用操作符“+”，“-”，例子如下：

SELECT “2016-01-01” - INTERVAL 1 SECOND 
    SELECT “2016-01-01” - INTERVAL 1 DAY 
    SELECT ‘2016-12-31 23:59:59' + INTERVAL 1 SECOND 
    SELECT ‘2016-12-31 23:59:59' + INTERVAL “1:1” MINUTE_SECOND

> 2015-12-31 23:59:59  
> 2015-12-31  
> 2017-01-01 00:00:00  
> 2017-01-01 00:01:00

[20201028190211204.png_pic_center]: /images/20230810/38470d25cf314e62845f80de69262f4e.png
[20201028190404221.png_pic_center]: /images/20230810/d0f6c4ffa0d64d58a2ad154a59c0e9e5.png
[20201028190426570.png_pic_center]: /images/20230810/b46a02f0f3b441549451141ddace2d98.png
[20200513165219852.png]: /images/20230810/1499f905b8d04d6888479ca733102260.png
[20200513165211581.png]: /images/20230810/e4b93cbea0524ddc94e2079f42ae7994.png