mysql-Query Day, week, January, year, and MySQL basic date function

Query Day:

Select *  from Table where = To_days (now ()); Select *  from Table where =

Query one week:

Select *  from Table  where 7  Day <= Date (column_time);

Query one months:

Select *  from Table  where 1 MONTH <= Date (column_time);

MySQL date and time functions
The query selects records for all Date_col values within the last 30 days.

MySQL>SELECTfromWHERE-<=// really convenient, Used to write their own, unexpectedly do not know there is this, failure.

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-in-Geneva');  - 3

WEEKDAY (date)

Returns the week index of date (0 = Monday, 1 = Tuesday, ...). 6 = Sunday):

Mysql> SELECTWEEKDAY ('1998- Geneva-Geneva  A: at:xx'); - 1MySQL> SELECTWEEKDAY ('1997- One- to'); - 2

DayOfMonth (date)

The return date is the day ordinal of January, ranging from 1 to 31:

MySQL>SELECT dayofmonth ('1998--Geneva -- ');  - 3

DayOfYear (date)

Return date is the day of the year, ranging from 1 to 366:

MySQL>SELECT dayofyear ('1998-in-Geneva');  -  the

MONTH (date)

Returns the month in date, ranging from 1 to 12:

MySQL>SELECTMONTH('1998-in-Geneva') ;  - 2

Dayname (date)

Returns the week name of date:

MySQL>Select dayname ("1998----)";  - ' Thursday '

MONTHNAME (date)

Returns the month name for date:

MySQL>Select MONTHNAME ("1998----)";  - ' February '

QUARTER (date)

Returns a date in the quarter of a year, ranging from 1 to 4:

MySQL>SELECT QUARTER ('98--");  - 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> SELECTWEEK ('1998- Geneva- -'); - 7MySQL> SELECTWEEK ('1998- Geneva- -’,0); - 7MySQL> SELECTWEEK ('1998- Geneva- -’,1); - 8MySQL> SELECTWEEK ('1998- A- to’,1); -  -

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(’ -- on- on'), WEEK (' -- on- on’,0); -  -,0MySQL> SELECTWEEK (' -- on- on’,2); -  the

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> SELECTYearweek (' -- on- on'); - 199952MySQL> SELECTMID (Yearweek (' -- on- on’),5,2); -  the

Year (date)

Returns the year of date with a range of 1000 to 9999:

MySQL>SELECTyear ('98-in-Geneva') ;  - 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-to-n ');  - 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 ('ten::');  - Ten

MINUTE (Time)

Returns the minute value of time, ranging from 0 to 59:

MySQL>SELECT MINUTE ('98----):  :");  - 5

SECOND (Time)

Returns the seconds value of time, ranging from 0 to 59:

MySQL>SELECT SECOND ('ten::');  - 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);  -  One

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.

 

mysql-Query Day, week, January, year, and MySQL basic date function