MySQL日期函数与日期转换格式化函数大全

2023-09-15 21:25:05 194

Mysql作为一款开元的免费关系型数据库，用户基础非常庞大，本文列出了MYSQL常用日期函数与日期转换格式化函数

1、DAYOFWEEK(date)

SELECTDAYOFWEEK(‘2016-01-16')
SELECTDAYOFWEEK(‘2016-01-1600:00:00')

->7(表示返回日期date是星期几，记住：星期天=1，星期一=2，...星期六=7)

2、WEEKDAY(date)

SELECTWEEKDAY(‘2016-01-16')
SELECTWEEKDAY(‘2016-01-1600:00:00')

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

3、DAYOFMONTH(date)

SELECTDAYOFMONTH(‘2016-01-16')
SELECTDAYOFMONTH(‘2016-01-1600:00:00')

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

4、DAYOFYEAR(date)

SELECTDAYOFYEAR(‘2016-03-31')
SELECTDAYOFYEAR(‘2016-03-3100:00:00')

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

5、MONTH(date)

SELECTMONTH(‘2016-01-16')
SELECTMONTH(‘2016-01-1600:00:00')

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

6、DAYNAME(date)

SELECTDAYNAME(‘2016-01-16')
SELECTDAYNAME(‘2016-01-1600:00:00')

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

7、MONTHNAME(date)

SELECTMONTHNAME(‘2016-01-16')
SELECTMONTHNAME(‘2016-01-1600:00:00')

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

8、QUARTER(date)

SELECTQUARTER(‘2016-01-16')
SELECTQUARTER(‘2016-01-1600:00:00')

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

9、WEEK(date，index)

SELECTWEEK(‘2016-01-03')
SELECTWEEK(‘2016-01-03',0)
SELECTWEEK(‘2016-01-03',1)

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

10、YEAR(date)

SELECTYEAR(‘70-01-16')
SELECTYEAR(‘2070-01-16')
SELECTYEAR(‘69-01-1600:00:00')

->1970(表示返回date的4位数年份)
->2070
->1969

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

11、HOUR(time)

SELECTHOUR(‘11:22:33')
SELECTHOUR(‘2016-01-1611:22:33')

->11
->11

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

12、MINUTE(time)

SELECTMINUTE(‘11:22:33')
SELECTMINUTE(‘2016-01-1611:44:33')

->22
->44

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

13、SECOND(time)

SELECTSECOND(‘11:22:33')
SELECTSECOND(‘2016-01-1611:44:22')

->33
->22

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

14、PERIOD_ADD(month，add)

SELECTPERIOD_ADD(1601,2)
SELECTPERIOD_ADD(191602,3)
SELECTPERIOD_ADD(191602,-3)

->201603
->191605
->191511

该函数返回对month做增减的操作结果，month的格式为yyMM或者yyyyMM,返回的都是yyyyMM格式的结果，add可以传负值

15、PERIOD_DIFF(monthStart，monthEnd)

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

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

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

16、DATE_ADD(date，INTERVALnumbertype)，同ADDDATE()

SELECTDATE_ADD(“2015-12-3123:59:59”,INTERVAL1SECOND)
SELECTDATE_ADD(“2015-12-3123:59:59”,INTERVAL1DAY)
SELECTDATE_ADD(“2015-12-3123:59:59”,INTERVAL“1:1”MINUTE_SECOND)
SELECTDATE_ADD(“2016-01-0100:00:00”,INTERVAL“-110”DAY_HOUR)

->2016-01-0100:00:00
->2016-01-0123:59:59
->2016-01-0100:01:00
->2015-12-3014:00:00

DATE_ADD()和ADDDATE()返回对date操作的结果

1、date的格式可以是“15-12-31”，可以是“15-12-3123:59:59”,也可以是“2015-12-3123: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天和小时"DAYSHOURS"
YEAR_MONTH年和月"YEARS-MONTHS"
HOUR_SECOND小时,分钟，"HOURS:MINUTES:SECONDS"
DAY_MINUTE天,小时,分钟"DAYSHOURS:MINUTES"
DAY_SECOND天,小时,分钟,秒"DAYSHOURS:MINUTES:SECONDS"

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

SELECT“2016-01-01”-INTERVAL1SECOND
SELECT“2016-01-01”-INTERVAL1DAY
SELECT‘2016-12-3123:59:59'+INTERVAL1SECOND
SELECT‘2016-12-3123:59:59'+INTERVAL“1:1”MINUTE_SECOND

返回结果：

->2015-12-3123:59:59
->2015-12-31
->2017-01-0100:00:00
->2017-01-0100:01:00

17、DATE_SUB(date，INTERVALnumbertype)，同SUBDATE()

用法和DATE_ADD()与ADDDATE()类似，一个是加，一个是减，用时参照16点，具体用法请参考DATE_ADD()与ADDDATE()。

18、TO_DAYS(date)

SELECTTO_DAYS(‘2016-01-16')
SELECTTO_DAYS(‘20160116')
SELECTTO_DAYS(‘160116')

->736344
->736344
->736344

返回西元0年至日期date是总共多少天

19、FROM_DAYS(date)

SELECTFROM_DAYS(367)

->0001-01-02

返回西元0年至今多少天的DATE值

20、DATE_FORMAT(date，format)：根据参数对date进行格式化。

SELECTDATE_FORMAT(‘2016-01-1622:23:00','%W%M%Y')
SELECTDATE_FORMAT(‘2016-01-1622:23:00','%D%y%a%d%m%b%j')
SELECTDATE_FORMAT(‘2016-01-1622:23:00','%H%k%I%r%T%S%w')
SELECTDATE_FORMAT(‘2016-01-1622:23:00','%Y-%m-%d%H:%i:%s')

->SaturdayJanuary2016
->16th16Sat1601Jan016
->22221010:23:00PM22:23:00006
->2016-01-1622:23:00

format的格式都列出来：

%M月名字(January……December)
%W星期名字(Sunday……Saturday)
%D有英语前缀的月份的日期(1st,2nd,3rd,等等。）
%Y年,数字,4位
%y年,数字,2位
%a缩写的星期名字(Sun……Sat)
%d月份中的天数,数字(00……31)
%e月份中的天数,数字(0……31)
%m月,数字(01……12)
%c月,数字(1……12)
%b缩写的月份名字(Jan……Dec)
%j一年中的天数(001……366)
%H小时(00……23)
%k小时(0……23)
%h小时(01……12)
%I小时(01……12)
%l小时(1……12)
%i分钟,数字(00……59)
%r时间,12小时(hh:mm:ss[AP]M)
%T时间,24小时(hh:mm:ss)
%S秒(00……59)
%s秒(00……59)
%pAM或PM
%w一个星期中的天数(0=Sunday……6=Saturday）
%U星期(0……52),这里星期天是星期的第一天
%u星期(0……52),这里星期一是星期的第一天
%%字符%)

TIME_FORMAT(time,format)：
具体用法和DATE_FORMAT()类似,但TIME_FORMAT只处理小时、分钟和秒(其余符号产生一个NULL值或0)

21、获取系统当前日期

SELECTCURDATE()
SELECTCURRENT_DATE()

->2016-01-16
->2016-01-16

22、获取系统当前时间

SELECTCURTIME()
SELECTCURRENT_TIME()

->17:44:22
->17:44:22

23、NOW()，SYSDATE()，CURRENT_TIMESTAMP()，LOCALTIME()：获取系统当前日期和时间

SELECTNOW()
SELECTSYSDATE()
SELECTCURRENT_TIMESTAMP()
SELECTCURRENT_TIMESTAMP
SELECTLOCALTIME()
SELECTLOCALTIME

->2016-01-1617:44:41
->2016-01-1617:44:41
->2016-01-1617:44:41
->2016-01-1617:44:41
->2016-01-1617:44:41
->2016-01-1617:44:41

24、UNIX_TIMESTAMP（date）：获取时间戳

SELECTUNIX_TIMESTAMP()
SELECTUNIX_TIMESTAMP(‘2016-01-16')
SELECTUNIX_TIMESTAMP(‘2016-01-1623:59:59')

->1452937627
->1452873600
->1452959999

25、FROM_UNIXTIME(unix_timestamp,format)：把时间戳转化成日期时间

SELECTFROM_UNIXTIME(1452959999)
SELECTFROM_UNIXTIME(1452959999,'%Y-%m-%d%H:%i:%s')

->2016-01-1623:59:59
->2016-01-1623:59:59

26、SEC_TO_TIME(seconds)：把秒数转化成时间

SELECTSEC_TO_TIME(2378)

->00:39:38

27、TIME_TO_SEC(time)：把时间转化成秒数

SELECTTIME_TO_SEC(‘22:23:00')

->2378

28、ADDTIME(time，times)：把times加到time上

SELECTADDTIME(“2015-12-3123:59:59”,'01:01:01')

->2016-01-0101:01:00

29、CONVERT_TZ(date,from_tz,to_tz)：转换时区

SELECTCONVERT_TZ(‘2004-01-0112:00:00','+00:00','+10:00')

->2004-01-0122:00:00

30、STR_TO_DATE(date，format)：将字符串转成format格式的日期时间

SELECTSTR_TO_DATE(‘2015-01-01',‘%Y-%m-%d')

->2015-01-01

31、LAST_DAY(date)：获取date当月最后一天的日期

SELECTLAST_DAY(SYSDATE())
SELECTLAST_DAY(‘2015-02-02')
SELECTLAST_DAY(‘2015-02-0200:22:33')

->2016-01-31
->2015-02-28
->2015-02-28

32、MAKEDATE(year,dayofyear)：根据参数（年份，第多少天）获取日期

SELECTMAKEDATE(2015,32)

->2015-02-01

33、MAKETIME(hour,minute,second)：根据参数（时，分，秒）获取时间

SELECTMAKETIME(12,23,34)

->12:23:34

34、YEARWEEK(date)：获取日期的年和周

SELECTYEARWEEK(SYSDATE())
SELECTYEARWEEK(‘2015-01-10')
SELECTYEARWEEK(‘2015-01-10',1)

->201602
->201501
->201502

35、WEEKOFYEAR(date)：获取当日是当年的第几周

SELECTWEEKOFYEAR(SYSDATE())
SELECTWEEKOFYEAR(‘2015-01-10')

->2
->2

->2
->2

mysql中常用的几种时间格式转换函数整理如下

1，from_unixtime(timestamp,format)：

timestamp为int型时间，如14290450779；format为转换的格式，包含格式如下：

2，unix_timestamp(date)：

作用与from_unixtime()刚好相反，前者是把unix时间戳转换为可读的时间，而unix_timestamp()是把可读的时间转换为unix时间戳，这在对datetime存储的时间进行排序时会用到。如unix_timestamp('2009-08-0610:10:40')，得到1249524739。

如果unix_timestamp()不传参数，则调用now()函数自动取当前时间。

3，date_format(date,format)：

date_format()是将date或datetime类型值转换为任意的时间格式。比如常见的应用场景，某表有一个字段是更新时间，存储的是datetime类型，但前台展示时只需要显示年月日（xxxx-xx-xx），这个时候就可以用date_format(date,'%Y-%m-%d')处理，而不需要在结果集中用程序循环处理。

MySQL日期函数与日期转换格式化函数大全

热门推荐

随机推荐