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的是当年第几月的英文名字)
SELECT QUARTER(‘2016-01-16') SELECT QUARTER(‘2016-01-16 00:00:00')
-> 1 (表示返回date的是当年的第几个季度，返回1,2,3,4)
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号周一才是第二周的开始日)
SELECT YEAR(‘70-01-16') SELECT YEAR(‘2070-01-16') SELECT YEAR(‘69-01-16 00:00:00')
-> 1970 (表示返回date的4位数年份) -> 2070 -> 1969
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
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!