Detailed explanation of MySQL date functions

As a free relational database from Kaiyuan, Mysql has a very large user base. This article lists the commonly used date functions and date conversion formatting functions in MYSQL. I hope it can help everyone.

1、DAYOFWEEK(date)

SELECT DAYOFWEEK(‘2016-01-16') 
SELECT DAYOFWEEK(‘2016-01-16 00:00:00')

-> 7 (表示，记住：星期天=1，星期一=2， ... 星期六=7)

2、WEEKDAY (date)

SELECT WEEKDAY(‘2016-01-16') 
SELECT WEEKDAY(‘2016-01-16 00:00:00')

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

3、DAYOFMONTH(date)

SELECT DAYOFMONTH(‘2016-01-16') 
SELECT DAYOFMONTH(‘2016-01-16 00:00:00')

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

4、DAYOFYEAR(date)

SELECT DAYOFYEAR(‘2016-03-31') 
SELECT DAYOFYEAR(‘2016-03-31 00:00:00')

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

5、MONTH(date)

SELECT MONTH(‘2016-01-16') 
SELECT MONTH(‘2016-01-16 00:00:00')

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

6.DAYNAME(date)

##

SELECT DAYNAME(‘2016-01-16') 
SELECT DAYNAME(‘2016-01-16 00:00:00')

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

7.MONTHNAME(date)

##

SELECT MONTHNAME(‘2016-01-16') 
SELECT MONTHNAME(‘2016-01-16 00:00:00')

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

8、QUARTER(date)

SELECT QUARTER(‘2016-01-16') 
SELECT QUARTER(‘2016-01-16 00:00:00')

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

9、WEEK(date,index)

SELECT WEEK(‘2016-01-03') 
SELECT WEEK(‘2016-01-03', 0) 
SELECT WEEK(‘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)

SELECT YEAR(‘70-01-16') 
SELECT YEAR(‘2070-01-16') 
SELECT YEAR(‘69-01-16 00:00:00')

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

It should be noted that if The year has only two digits, so the automatic completion mechanism is based on the default time of 1970.01.01, and the completion of >= 70 is 19, and the completion of < 70 is 20

11, HOUR(time)

SELECT HOUR(‘11:22:33') 
SELECT HOUR(‘2016-01-16 11:22:33')

-> 11
-> 11

Returns the hour value of the date or time, value range (0-23)

12. MINUTE(time)

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

-> 22
-> 44

Returns the minute value of the time, Value range (0-59)

13, SECOND(time)

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

-> 33
-> 22

Return the minute value of the time, value range (0-59)

14, PERIOD_ADD(month, add)

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

-> 201603
-> 191605
-> 191511

This function returns the operation result of increasing or decreasing month. The format of month is yyMM or yyyyMM. The results returned are the results in yyyyMM format. Add can pass negative values

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)

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

This function returns monthStart - monthEnd The number of months between intervals

16, DATE_ADD(date, INTERVAL number type), the same as 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)

-> 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() and ADDDATE() return the results of date operations

1. The format of date can be "15-12-31" or "15-12-31 23 :59:59", or "2015-12-31 23:59:59". If the parameter date is in date format, the date format result is returned. If the parameter date is in datetime format, the datetime format result is returned

2. type format:

SECOND seconds SECONDS

MINUTE minutes MINUTES

HOUR time HOURS
DAY days DAYS
MONTH months MONTHS
YEAR years YEARS
MINUTE_SECOND minutes and Seconds"MINUTES:SECONDS"
HOUR_MINUTE Hours and minutes "HOURS:MINUTES"
DAY_HOUR Days and hours "DAYS HOURS"
YEAR_MONTH Years and months "YEARS-MONTHS"
HOUR_SECOND Hours, minutes, " HOURS:MINUTES:SECONDS"
DAY_MINUTE days, hours, minutes"DAYS HOURS:MINUTES"
DAY_SECOND days, hours, minutes, seconds"DAYS HOURS:MINUTES:SECONDS"


3 , In addition, if you do not use a function, you can also consider using the operators "+" and "-". The examples are as follows:

##

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

Return result:

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

17. DATE_SUB(date, INTERVAL number type), same as SUBDATE()

Usage is similar to DATE_ADD() and ADDDATE(), One is addition and the other is subtraction. Please refer to 16 points for the usage. Please refer to DATE_ADD() and ADDDATE() for specific usage.

18、TO_DAYS(date)

SELECT TO_DAYS(‘2016-01-16') 
SELECT TO_DAYS(‘20160116') 
SELECT TO_DAYS(‘160116')

-> 736344
-> 736344
-> 736344

Return to 0 How many days is the total number of days from year to date

19、FROM_DAYS(date)

SELECT FROM_DAYS(367)

-> 0001-01-02

Return the DATE value of the number of days since AD ​​0

20. DATE_FORMAT(date, format): Format the date according to the parameters. The formats of

##

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')

-> Saturday January 2016
-> 16th 16 Sat 16 01 Jan 016
-> 22 22 10 10:23:00 PM 22:23:00 00 6
-> 2016-01-16 22:23:00

format are listed:

%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)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 字符% )

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

21、获取系统当前日期

SELECT CURDATE() 
SELECT CURRENT_DATE()

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

22、获取系统当前时间

SELECT CURTIME() 
SELECT CURRENT_TIME()

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

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

SELECT NOW() 
SELECT SYSDATE() 
SELECT CURRENT_TIMESTAMP() 
SELECT CURRENT_TIMESTAMP 
SELECT LOCALTIME() 
SELECT LOCALTIME

-> 2016-01-16 17:44:41
-> 2016-01-16 17:44:41
-> 2016-01-16 17:44:41
-> 2016-01-16 17:44:41
-> 2016-01-16 17:44:41
-> 2016-01-16 17:44:41

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

SELECT UNIX_TIMESTAMP() 
SELECT UNIX_TIMESTAMP(‘2016-01-16') 
SELECT UNIX_TIMESTAMP(‘2016-01-16 23:59:59')

-> 1452937627
-> 1452873600
-> 1452959999

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

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

-> 2016-01-16 23:59:59
-> 2016-01-16 23:59:59

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

SELECT SEC_TO_TIME(2378)

-> 00:39:38

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

SELECT TIME_TO_SEC(‘22:23:00')

-> 2378

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

SELECT ADDTIME(“2015-12-31 23:59:59”,'01:01:01')

-> 2016-01-01 01:01:00

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

SELECT CONVERT_TZ(‘2004-01-01 12:00:00','+00:00','+10:00')

-> 2004-01-01 22:00:00

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

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

-> 2015-01-01

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

SELECT LAST_DAY(SYSDATE()) 
SELECT LAST_DAY(‘2015-02-02') 
SELECT LAST_DAY(‘2015-02-02 00:22:33')

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

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

SELECT MAKEDATE(2015 ,32)

-> 2015-02-01

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

SELECT MAKETIME(12 ,23 ,34 )

-> 12:23:34

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

SELECT YEARWEEK(SYSDATE()) 
SELECT YEARWEEK(‘2015-01-10') 
SELECT YEARWEEK(‘2015-01-10',1)

-> 201602
-> 201501
-> 201502

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

SELECT WEEKOFYEAR(SYSDATE()) 
SELECT WEEKOFYEAR(‘2015-01-10')

-> 2
-> 2

-> 2
-> 2

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

1，from_unixtime(timestamp, format)：

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

%M Month name (January...December)
%W Week name (Sunday...Saturday)
%D Day of the month with English prefix (1st, 2nd, 3rd, etc.)
%Y Year, number, 4 digits
%y Year, number, 2 digits
%a Abbreviated name of the week (Sun...Sat)
%d Number of days in the month, number (00… …31)
%e Number of days in the month, number (0...31)
%m Month, number (01...12)
%c Month, number (1...12)
%b Abbreviated month name (Jan...Dec)
%j Number of days in a year (001...366)
%H hours (00...23)
%k hours (0... …23)
%h hour (01…12)
%I hour (01…12)
%l hour (1…12)
%i minute, number (00… …59)
%r Time, 12 hours (hh:mm:ss [AP]M)
%T Time, 24 hours (hh:mm:ss)
%S seconds (00……59 )
%s Seconds (00...59)
%p AM or PM
%w Number of days in a week (0=Sunday...6=Saturday)
%U Week (0... …52), here Sunday is the first day of the week
%u week (0...52), here Monday is the first day of the week

2, unix_timestamp(date):

The function is exactly the opposite of from_unixtime(). The former converts the unix timestamp into a readable time, while unix_timestamp() converts the readable time into a unix timestamp. This is useful for datetime storage. It is used when sorting by time. For example, unix_timestamp('2009-08-06 10:10:40'), you get 1249524739.

If unix_timestamp() does not pass parameters, call the now() function to automatically get the current time.

3, date_format(date, format):

date_format() converts date or datetime type values ​​into any time format. For example, in a common application scenario, a table has a field that is the update time and stores the datetime type. However, when displayed in the frontend, it only needs to display the year, month and day (xxxx-xx-xx). In this case, you can use date_format(date,'% Y-%m-%d ') processing without the need to use program loop processing in the result set.

Related recommendations:

Commonly used mysql date functions

mysql date and time types

php mysql date operation function_PHP tutorial

The above is the detailed content of Detailed explanation of MySQL date functions. For more information, please follow other related articles on the PHP Chinese website!