mysql-Date function

Because now the project with the database MySQL, so it must be studied on the processing of time, how to deal with the month, year, day, time, how to query, polymerization time, etc., through the search information, is summarized with the next, so that from time to moment only:
MySQL Date and time functions
Here is an example of using a date function. The following query selects records for all Date_col values 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). Index values conform to the ODBC standard.
mysql> SELECT DAYOFWEEK (' 1998-02-03 ');
3

WEEKDAY (date)
Returns the week index of date (0 = Monday, 1 = Tuesday, ...). 6 = Sunday):
mysql> SELECT WEEKDAY (' 1998-02-03 22:23:00 ');
1
mysql> SELECT WEEKDAY (' 1997-11-05 ');
2

DayOfMonth (date)
The return date is the day ordinal of January, ranging from 1 to 31:
mysql> SELECT dayofmonth (' 1998-02-03 ');
3

DayOfYear (date)
Return date is the day of the year, ranging from 1 to 366:
mysql> SELECT dayofyear (' 1998-02-03 ');
34

MONTH (date)
Returns the month in date, ranging from 1 to 12:
mysql> SELECT MONTH (' 1998-02-03 ');
2

Dayname (date)
Returns the week name of date:
Mysql> Select Dayname ("1998-02-05");
' Thursday '

MONTHNAME (date)
Returns the month name for date:
Mysql> Select MONTHNAME ("1998-02-05");
' February '

QUARTER (date)
Returns a date in the quarter of a year, ranging from 1 to 4:
mysql> SELECT QUARTER (' 98-04-01 ');
2

WEEK (date)
WEEK (Date,first)
For Sunday is the first day of the week, if the function has only one parameter call, the return date is the week ordinal of the year, and the return value range is 0 to 53 (yes, there may be the beginning of the 53rd week). The two-Parameter form of WEEK () allows you to specify whether a week starts in Sunday or Monday, and the return value is 0-53 or 1-52. Here's a table showing how the second parameter works: value meaning
01 weeks starting in Sunday, the return value range is 0-53
1 a week starting in Monday, the return value range is 0-53
2 a week starting in Sunday, the return value range is 1-53
31 weeks starting in Monday, the return value range is 1-53 (ISO 8601)

mysql> SELECT WEEK (' 1998-02-20 ');
7
mysql> SELECT WEEK (' 1998-02-20 ', 0);
7
mysql> SELECT WEEK (' 1998-02-20 ', 1);
8
mysql> SELECT WEEK (' 1998-12-31 ', 1);
53

Note that in version 4.0, WEEK (#,0) was changed to match the USA calendar. Note that if the week is the last week of the previous year, when you do not use 2 or 3 as an optional parameter, MySQL will return 0:
mysql> SELECT year (' 2000-01-01 '), WEEK (' 2000-01-01 ', 0);
--2000, 0
mysql> SELECT WEEK (' 2000-01-01 ', 2);
52

You might argue that when a given date value is actually part of the 52nd Week of 1999, MySQL should return 52 to the WEEK () function. We decided to return 0 because we wanted the function to return "week of the specified year". This makes the use of the WEEK () function reliable when used in conjunction with other functions that extract the day value of a date value. If you prefer to get the appropriate year-week value, you should use the parameter 2 or 3 as an optional parameter, or use the function Yearweek ():
mysql> SELECT yearweek (' 2000-01-01 ');
199952
Mysql> SELECT MID (Yearweek (' 2000-01-01 '), 5,2);
52

Year (date)
Returns the year of date with a range of 1000 to 9999:
mysql> SELECT year (' 98-02-03 ');
1998

Yearweek (date)
Yearweek (Date,first)
Returns a date value which week of the year. The form and function of the second parameter is exactly the same as the second parameter of WEEK (). Note that for a given date parameter is the first or last week of the year, the returned year value may be inconsistent with the year given by the date parameter:
mysql> SELECT yearweek (' 1987-01-01 ');
198653

Note that for optional parameters 0 or 1, the return value of the week value differs from the value returned by the WEEK () function (0), and WEEK () returns the week value based on the given year context.
HOUR (Time)
Returns the time value, 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 seconds value of time, ranging from 0 to 59:
mysql> SELECT SECOND (' 10:05:03 ');
3

Period_add (P,n)
Add N months to the period P (in the form yymm or yyyymm). Returns a 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 the period P1 and P2. P1 and P2 should be specified with Yymm or yyyymm. Note that the time 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-instead of Date_add () and date_sub (for example, below). The parameter date is a DATETIME or date value that specifies the beginning of a date. Expr is an expression that specifies whether to increment or subtract the interval value from the start date. expr is a string; it can represent a negative interval value with a "-" lead. Type is a keyword that signifies that the expression is interpreted in including. The following table shows how the type and expr parameters are associated: The type value expr expects format
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
Day days
MONTH MONTHS
Year years
Minute_second "Minutes:seconds"
Hour_minute "Hours:minutes"
Day_hour "Days HOURS"
Year_month "Years-months"
Hour_second "HOURS:MINUTES:SECONDS"
Day_minute "Days Hours:minutes"
Day_second "Days HOURS:MINUTES:SECONDS"
In the format of expr, MySQL allows any character to be used as a delimiter. The recommended bounding characters are shown in the table. The return value is also a date value if the date parameter is a date value and the calculated interval has only the year, MONTH, and day portions (no time portion). Otherwise the return value is a DATETIME value:
mysql> Select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
-1998-01-01 00:00:00
Mysql> Select INTERVAL 1 day + "1997-12-31";
1998-01-01
mysql> Select "1998-01-01"-INTERVAL 1 SECOND;
-1997-12-31 23:59:59
Mysql> Select Date_add ("1997-12-31 23:59:59",
INTERVAL 1 SECOND);
-1998-01-01 00:00:00
Mysql> Select Date_add ("1997-12-31 23:59:59",
-INTERVAL 1 day);
-1998-01-01 23:59:59
Mysql> Select Date_add ("1997-12-31 23:59:59",
-INTERVAL "1:1" minute_second);
-1998-01-01 00:01:00
Mysql> Select Date_sub ("1998-01-01 00:00:00",
-INTERVAL "1 1:1:1" day_second);
-1997-12-30 22:58:59
Mysql> Select Date_add ("1998-01-01 00:00:00",
-INTERVAL "-1" day_hour);
-1997-12-30 14:00:00
Mysql> Select Date_sub ("1998-01-02", INTERVAL);
1997-12-02

This article is from the "Mr_computer" blog, make sure to keep this source http://caochun.blog.51cto.com/4497308/1546250

mysql-Date function