Detailed explanation of MySQL date functions-Mysql Tutorial-php.cn

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

Copy after login

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

Copy after login

2、WEEKDAY (date)

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

Copy after login

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

Copy after login

3、DAYOFMONTH(date)

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

Copy after login

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

Copy after login

4、DAYOFYEAR(date)

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

Copy after login

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

Copy after login

5、MONTH(date)

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

Copy after login

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

Copy after login

6.DAYNAME(date)

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

Copy after login

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

Copy after login

7.MONTHNAME(date)

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

Copy after login

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

Copy after login

8、QUARTER(date)

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

Copy after login

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

Copy after login

9、WEEK(date,index)

SELECT WEEK(‘2016-01-03') SELECT WEEK(‘2016-01-03', 0) SELECT WEEK(‘2016-01-03', 1)

Copy after login

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

Copy after login

10, YEAR(date)

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

Copy after login

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

Copy after login

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

Copy after login

-> 11 -> 11

Copy after login

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

Copy after login

-> 22 -> 44

Copy after login

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

Copy after login

-> 33 -> 22

Copy after login

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)

Copy after login

-> 201603 -> 191605 -> 191511

Copy after login

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)

Copy after login

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

Copy after login

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)

Copy after login

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

Copy after login

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

Copy after login

Return result:

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

Copy after login

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

Copy after login

-> 736344 -> 736344 -> 736344

Copy after login

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

19、FROM_DAYS(date)

SELECT FROM_DAYS(367)

Copy after login

-> 0001-01-02

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

22、获取系统当前时间

SELECT CURTIME() SELECT CURRENT_TIME()

Copy after login

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

Copy after login

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

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

Copy after login

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

Copy after login

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

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

Copy after login

-> 1452937627 -> 1452873600 -> 1452959999

Copy after login

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

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

Copy after login

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

Copy after login

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

SELECT SEC_TO_TIME(2378)

Copy after login

-> 00:39:38

Copy after login

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

SELECT TIME_TO_SEC(‘22:23:00')

Copy after login

-> 2378

Copy after login

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

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

Copy after login

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

Copy after login

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

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

Copy after login

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

Copy after login

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

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

Copy after login

-> 2015-01-01

Copy after login

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

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

Copy after login

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

Copy after login

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

SELECT MAKEDATE(2015 ,32)

Copy after login

-> 2015-02-01

Copy after login

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

SELECT MAKETIME(12 ,23 ,34 )

Copy after login

-> 12:23:34

Copy after login

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

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

Copy after login

-> 201602 -> 201501 -> 201502

Copy after login

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

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

Copy after login

-> 2 -> 2

Copy after login

-> 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!