Shulou(Shulou.com)06/01 Report--

This article mainly introduces mysql how to query a period of time records, the article is very detailed, has a certain reference value, interested friends must read it!

Mysql query the records of a period of time: 1. Query the records within N days with the code [WHERE TO_DAYS (NOW ())-TO_DAYS (time field) 1mysql > SELECT WEEKDAY ('1997-11-05');-> 2

DAYOFMONTH (date)

The date is returned on the day of the month, 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 of the date:

Mysql > SELECT MONTHNAME ("1998-02-05");-> 'February'

QUARTER (date)

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

Mysql > SELECT QUARTER (98-04-01');-> 2

WEEK (date)

WEEK (date,first)

For situations where Sunday is the first day of the week, if the function is called with only one argument, the week ordinal of the year is returned, with values ranging from 0 to 53 (yes, there may be the beginning of week 53). WEEK () in the form of two parameters allows you to specify whether the week begins on Sunday or Monday, and whether the return value is 0-53 or 1-52. A table here shows how the second parameter works:

Value meaning

The 0 week begins on Sunday, and the return value ranges from 0 to 53

1 the week begins on Monday, and the range of return values is 0-53

2 the week begins on Sunday and the range of return values is 1-53

3 the week begins on Monday and returns values ranging from 1 to 53 (ISO 8601)

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

Note that in version 4.0, WEEK (#, 0) is changed to match the USA calendar. Note that if the week is the last week of the previous year, MySQL returns 0 when you do not use 2 or 3 as an optional parameter:

Mysql > SELECT YEAR ('2000-01-01'), WEEK ('2000-01-01);-> 2000, 0mysql > SELECT WEEK (' 2000-01-01-01);-> 52

You might argue that when a given date value is actually part of week 52 in 1999, MySQL should return 52 to the WEEK () function. We decided to return 0 because we wanted the function to return "the week in a specified year". This makes the use of the WEEK () function reliable when used in conjunction with other functions that extract the value of the month from the date value. If you prefer to get the appropriate year-week value, you should use parameter 2 or 3 as an optional parameter, or use the function YEARWEEK ():

Mysql > SELECT YEARWEEK ('2000-01-01');-> 199952mysql > SELECT MID (YEARWEEK ('2000-01-01'), 5);-> 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 in which the date value is. The form and function of the second parameter is exactly the same as the second parameter of WEEK (). Note that if the given date parameter is the first or last week of the year, the year value returned may not be consistent with the year given by the date parameter:

Mysql > SELECT YEARWEEK ('1987-01-01');-> 198653

Note that for the optional parameter 0 or 1, the return value of the week value is different from that returned by the WEEK () function (0), and WEEK () returns the week value based on the given annual context.

HOUR (time)

Returns the hourly value of time, ranging from 0 to 23:

Mysql > SELECT HOUR ('10VOV 05RU 03');-> 10

MINUTE (time)

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

Mysql > SELECT MINUTE ('98-02-03 10 purl 05purl 03');-> 5

SECOND (time)

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

Mysql > SELECT SECOND ('10VR 05R 03');-> 3

PERIOD_ADD (PPJN)

Add N months to period P (format YYMM or YYYYMM). Returns the value in YYYYMM format. Note that the period parameter P is not a date value:

Mysql > SELECT PERIOD_ADD (9801 Magi 2);-> 199803

PERIOD_DIFF (P1and P2)

Returns the number of months between P1 and P2 during the period. P1 and P2 should be specified as 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 for DATE_ADD () and DATE_SUB (), respectively. In MySQL 3.23, if the right side of the expression is a date value or a date-time field, you can use + and-instead of DATE_ADD () and DATE_SUB () (example below). The parameter date is a DATETIME or DATE value that specifies the start of a date. Expr is an expression that specifies whether the interval value is increased or subtracted from the start date. Expr is a string; it can represent a negative interval value with a "-" header. Type is a keyword that indicates the format in which the expression is interpreted.

The above is how mysql query all the contents recorded over a period of time, thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.