Home  >  Article  >  Database  >  Detailed explanation of MySQL date functions

Detailed explanation of MySQL date functions

小云云
小云云Original
2018-03-03 10:48:351843browse

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

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn