MySQL Date and Time Functions

MySQL date functions and time functions are commonly used functions. The following describes the usage of MySQL date functions and time functions in detail. We hope you can have a better understanding of MySQL date functions.

Use the MySQL date function to query and select all records whose date_col values are within the last 30 days.
Mysql> SELECT something FROM tbl_name
WHERE TO_DAYS (NOW ()-TO_DAYS (date_col) <= 30.

DAYOFWEEK (date)
Returns the week index of date (1 = Sunday, 2 = Monday,... 7 = Saturday ). The index value complies with ODBC standards.
Mysql> select dayofweek ('2017-02-03 ');
-> 3

WEEKDAY (date)
Returns the week index of date (0 = Monday, 1 = Tuesday,... 6 = Sunday ):
Mysql> select weekday ('2017-02-03 22:23:00 ');
-> 1
Mysql> select weekday ('2017-11-05 ');
-> 2

DAYOFMONTH (date)
The return date is the day of January 1, January, ranging from 1 to 31:
Mysql> select dayofmonth ('2017-02-03 ');
-> 3

DAYOFYEAR (date)
Returns the day of the year from 1 to 366:
Mysql> select dayofyear ('2017-02-03 ');
-> 34

MONTH (date)
Returns the month from date, ranging from 1 to 12:
Mysql> select month ('2014-02-03 ');
-> 2

DAYNAME (date)
Returns the week name of date:
Mysql> select dayname ("1998-02-05 ");
-> 'Thursday'

MONTHNAME (date)
Returns the month name of date:
Mysql> select monthname ("1998-02-05 ");
-> 'February'

QUARTER (date)
Returns the quarter of a year in which the date value ranges from 1 to 4:
Mysql> select quarter ('98-04-01 ');
-> 2

WEEK (date)
WEEK (date, first)
For Sunday is the first day of a week, if the function only calls one parameter, return the week of the year for date, and the returned value range is 0 to 53 (yes, may start from 53rd weeks ). The WEEK () parameter allows you to specify whether a WEEK starts on Sunday or Monday, and whether the returned value is 0-53 or 1-52. Here, a table shows how the second parameter works:

Value Meaning
0 a week starts on Sunday, and the returned value range is 0-53
1 A week starts on Monday, and the returned value range is 0-53
2 A week starts on Sunday, and the returned value range is 1-53.
3 a week starts on Monday, and the returned value range is 1-53 (ISO 8601)

Mysql> select week ('2017-02-20 ');
-> 7
Mysql> select week ('2017-02-20 ', 0 );
-> 7
Mysql> select week ('2017-02-20 ', 1 );
-> 8
Mysql> select week ('2017-12-31 ', 1 );
-> 53

Note that in version 4.0, WEEK (#, 0) is changed to match the USA calendar. Note: If a week is the last week of the previous year, MySQL returns 0 if you do not use 2 or 3 as an optional parameter:
Mysql> select year ('2014-01-01 '), WEEK ('2014-01-01', 0 );
-> 2000, 0
Mysql> select week ('2017-01-01 ', 2 );
-> 52

You may argue that when the given date value is actually part of WEEK 1999 of 52nd, MySQL should return 52 for the WEEK () function. We decided to return 0 because we wanted the function to return "the week in the specified year ". This makes the use of WEEK () function reliable when used with other functions that extract the values of the month and day from the date value. If you want to get the appropriate year-week value, you should use parameter 2 or 3 as the optional parameter, or use the YEARWEEK () function ():
Mysql> select yearweek ('2017-01-01 ');
-> 199952
Mysql> select mid (YEARWEEK ('2017-01-01 '), 5, 2 );
-> 52

YEAR (date)
Returns the year of date, ranging from 1000 to 9999:
Mysql> select year ('98-02-03 ');
-> 1998

YEARWEEK (date)
YEARWEEK (date, first)
Returns the week of the year of the date value. The form and function of the second parameter are exactly the same as that of the second parameter of WEEK. Note: If the given date parameter is the first week or the last week of the year, the returned year value may be different from the year given by the date parameter:
Mysql> select yearweek ('2017-01-01 ');
-> 198653

Note that for optional parameters 0 or 1, the return value of the weekly value is different from that of the WEEK () function. WEEK () returns the weekly value based on the given yearly context.
HOUR (time)
Returns the hour value of time, ranging from 0 to 23:
Mysql> select hour ('10: 05: 03 ');
-> 10

MINUTE (time)
Returns the minute value of time, ranging from 0 to 59:
Mysql> select minute ('98-02-03 10:05:03 ');
-> 5

SECOND (time)
Returns the second value of time, ranging from 0 to 59:
Mysql> select second ('10: 05: 03 ');
-> 3

PERIOD_ADD (P, N)
Add N months to period P in the format of YYMM or YYYYMM. Return Value in YYYYMM format. Note that the period parameter P is not a date value:
Mysql> SELECT PERIOD_ADD (9801,2 );
-> 199803

PERIOD_DIFF (P1, P2)
Returns the number of months between P1 and P2. P1 and P2 should be specified in YYMM or YYYYMM. Note that the period parameters P1 and P2 are not date values:
Mysql> SELECT PERIOD_DIFF (9802,199703 );
-> 11

DATE_ADD (date, INTERVAL expr type)
DATE_SUB (date, INTERVAL expr type)
ADDDATE (date, INTERVAL expr type)
SUBDATE (date, INTERVAL expr type)
These functions perform arithmetic operations on dates. ADDDATE () and SUBDATE () are synonyms of DATE_ADD () and DATE_SUB () respectively. In MySQL 3.23, if the right side of the expression is a date value or a datetime field, you can use + and-to replace DATE_ADD () and DATE_SUB () (example ). The date parameter is a DATETIME or DATE value that specifies the start of a date. Expr is an expression that specifies whether to increase or subtract the interval value from the start date. Expr is a string. It can lead by a "-" to indicate a negative interval value. Type is a keyword that indicates the format in which the expression is interpreted.
 

Common MySQL command line tools

Implementation of MySQL non-repeated Query

Five common MySQL command lines

How MySQL query optimizer works

How to adjust the MySQL Query Buffer