Commonly used mysql date functions

Because I am about to graduate, I have been to several companies for interviews, including one interview for PHP development. Youdao sql has optimization questions. At that time, I was interested in mysql ’s function has not been studied and studied systematically, embarrassing. . .
mysql has the main built-in functions as follows:

NOW()
Returns the current date and time

CURDATE()
Returns the current The date

CURTIME()
Returns the current time

DATE()
Extracts the date or date/time The date part of the expression

EXTRACT()
Return the individual parts of the date/time

DATE_ADD()
Add the specified time interval to the date


DATE_SUB()
Subtract the specified time interval from the date


DATEDIFF()
Returns the number of days between two dates


DATE_FORMAT()
Display date/time in different formats


Also includes some partial functions that return dates :
Select various parts of date and time: date, time, year, quarter, month, day, hour, minute, second, microsecond
set @dt = '2008-09-10 07:15 :30.123456';

select date(@dt); -- 2008-09-10
select time(@dt); -- 07:15:30.123456
select year(@dt) ; -- 2008
select quarter(@dt); -- 3
select month(@dt); -- 9
select week(@dt); -- 36
select day(@ dt); -- 10
select hour(@dt); -- 7
select minute(@dt); -- 15
select second(@dt); -- 30
select microsecond (@dt); -- 123456
These functions are very helpful for comparing dates. Many novices are not very good at handling date functions and are prone to errors. Here is an example:
If there is The table product has a field add_time, and its data type is datetime. Someone may write sql like this:
select * from product where add_time = '2013-01-12'
For this This statement, if the format you store is YY-mm-dd, then OK, if the format you store is: 2013-01-12 23:23:56, you will be in tragedy, this is what you will do You can use the DATE() function to return the date part, so this sql should be processed as follows:
select * from product where Date(add_time) = '2013-01-12'
Here is another one, if you want How about the products added in January 2013?
select * from product where date(add_time) between '2013-01-01' and '2013-01-31'
You can also write like this:
select * from product where Year(add_time) = 2013 and Month(add_time) = 1
You should know the role of mysql date function in dealing with date comparison problems, right?

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