MySQL日期函数大全
1、DAYOFWEEK(date)
SELECT DAYOFWEEK(‘2016-01-16’)
SELECT DAYOFWEEK(‘2016-01-16 00:00:00’)
2、WEEKDAY(date)
SELECT WEEKDAY(‘2016-01-16’)
SELECT WEEKDAY(‘2016-01-16 00:00:00’)
3、DAYOFMONTH(date)
SELECT DAYOFMONTH(‘2016-01-16’)
SELECT DAYOFMONTH(‘2016-01-16 00:00:00’)
4、DAYOFYEAR(date)
SELECT DAYOFYEAR(‘2016-03-31’)
SELECT DAYOFYEAR(‘2016-03-31 00:00:00’)
5、MONTH(date)
SELECT MONTH(‘2016-01-16’)
SELECT MONTH(‘2016-01-16 00:00:00’)
6、DAYNAME(date)
SELECT DAYNAME(‘2016-01-16’)
SELECT DAYNAME(‘2016-01-16 00:00:00’)
7、MONTHNAME(date)
SELECT MONTHNAME(‘2016-01-16’)
SELECT MONTHNAME(‘2016-01-16 00:00:00’)
8、QUARTER(date)
SELECT QUARTER(‘2016-01-16’)
SELECT QUARTER(‘2016-01-16 00:00:00’)
9、WEEK(date,index)
SELECT WEEK(‘2016-01-03’)
SELECT WEEK(‘2016-01-03’, 0)
SELECT WEEK(‘2016-01-03’, 1)
10、YEAR(date)
SELECT YEAR(‘70-01-16’)
SELECT YEAR(‘2070-01-16’)
SELECT YEAR(‘69-01-16 00:00:00’)
11、HOUR(time)
SELECT HOUR(‘11:22:33’)
SELECT HOUR(‘2016-01-16 11:22:33’)
12、MINUTE(time)
SELECT MINUTE(‘11:22:33’)
SELECT MINUTE(‘2016-01-16 11:44:33’)
13、SECOND(time)
SELECT SECOND(‘11:22:33’)
SELECT SECOND(‘2016-01-16 11:44:22’)
14、PERIOD_ADD(month,add)
SELECT PERIOD_ADD(1601,2)
SELECT PERIOD_ADD(191602,3)
SELECT PERIOD_ADD(191602,-3)
15、PERIOD_DIFF(monthStart,monthEnd)
SELECT PERIOD_DIFF(1601,1603)
SELECT PERIOD_DIFF(191602,191607)
SELECT PERIOD_DIFF(1916-02,1916-07)
SELECT PERIOD_DIFF(1602,9002)
16、DATE_ADD(date,INTERVAL number type),同 ADDDATE()
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)
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
返回结果:
17、DATE_SUB(date,INTERVAL number type),同 SUBDATE()
18、TO_DAYS(date)
SELECT TO_DAYS(‘2016-01-16’)
SELECT TO_DAYS(‘20160116’)
SELECT TO_DAYS(‘160116’)
19、FROM_DAYS(date)
SELECT FROM_DAYS(367)
20、DATE_FORMAT(date,format):根据参数对date进行格式化。
SELECT DATE_FORMAT(‘2016-01-16 22:23:00’,’%W %M %Y’)
SELECT DATE_FORMAT(‘2016-01-16 22:23:00’,’%D %y %a %d %m %b %j’)
SELECT DATE_FORMAT(‘2016-01-16 22:23:00’,’%H %k %I %r %T %S %w’)
SELECT DATE_FORMAT(‘2016-01-16 22:23:00’,’%Y-%m-%d %H:%i:%s’)
format的格式都列出来:
TIME_FORMAT(time,format):
具体用法和DATE_FORMAT()类似,但TIME_FORMAT只处理小时、分钟和秒(其余符号产生一个NULL值或0)
21、获取系统当前日期
SELECT CURDATE()
SELECT CURRENT_DATE()
22、获取系统当前时间
SELECT CURTIME()
SELECT CURRENT_TIME()
23、NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME():获取系统当前日期和时间
SELECT NOW()
SELECT SYSDATE()
SELECT CURRENT_TIMESTAMP()
SELECT CURRENT_TIMESTAMP
SELECT LOCALTIME()
SELECT LOCALTIME
24、UNIX_TIMESTAMP(date):获取时间戳
SELECT UNIX_TIMESTAMP()
SELECT UNIX_TIMESTAMP(‘2016-01-16’)
SELECT UNIX_TIMESTAMP(‘2016-01-16 23:59:59’)
25、FROM_UNIXTIME(unix_timestamp,format):把时间戳转化成日期时间
SELECT FROM_UNIXTIME(1452959999)
SELECT FROM_UNIXTIME(1452959999,’%Y-%m-%d %H:%i:%s’)
26、SEC_TO_TIME(seconds):把秒数转化成时间
SELECT SEC_TO_TIME(2378)
27、TIME_TO_SEC(time):把时间转化成秒数
SELECT TIME_TO_SEC(‘22:23:00’)
28、ADDTIME(time,times):把times加到time上
SELECT ADDTIME(“2015-12-31 23:59:59”,’01:01:01’)
29、CONVERT_TZ(date,from_tz ,to_tz ):转换时区
SELECT CONVERT_TZ(‘2004-01-01 12:00:00’,’+00:00’,’+10:00’)
30、STR_TO_DATE(date,format ):将字符串转成format格式的日期时间
SELECT STR_TO_DATE(‘2015-01-01’, ‘%Y-%m-%d’)
31、LAST_DAY(date ):获取date当月最后一天的日期
SELECT LAST_DAY(SYSDATE())
SELECT LAST_DAY(‘2015-02-02’)
SELECT LAST_DAY(‘2015-02-02 00:22:33’)
32、MAKEDATE(year ,dayofyear ):根据参数(年份,第多少天)获取日期
SELECT MAKEDATE(2015 ,32)
33、 MAKETIME(hour ,minute ,second ):根据参数(时,分,秒)获取时间
SELECT MAKETIME(12 ,23 ,34 )
34、YEARWEEK(date):获取日期的年和周
SELECT YEARWEEK(SYSDATE())
SELECT YEARWEEK(‘2015-01-10’)
SELECT YEARWEEK(‘2015-01-10’,1)
35、WEEKOFYEAR(date):获取当日是当年的第几周
SELECT WEEKOFYEAR(SYSDATE())
SELECT WEEKOFYEAR(‘2015-01-10’)