Mysql time-based Query SQL statement Summary (1/2)

In the previous section, I talked about the SQL statement used to query a specified mysql date. Next I will summarize all the statements used to query mysql date.

Description: There is a member table, there is a birthday field, the value is 'yyyy-MM-DD 'format, now to query the birthday of a member within a period of time, for example, from '06-03' to '07-08 ', all the Members who celebrate their birthdays in this period.

SQL statement:

The Code is as follows:	Copy code
Select * From user Where DATE_FORMAT (birthday, '% m-% D')> = '06-03' and DATE_FORMAT (birthday, '% m-% D ') <= '07-08 ';

Note: The commonly used time and date processing functions are mainly used for the application of the DATE_FORMAT () function.

1. DAYOFWEEK (date)
Returns the index of the week of the date (1 = Sunday, 2 = Monday ,...... 7 = Saturday ). These index values correspond to the ODBC standard.

The Code is as follows:	Copy code
Mysql> select DAYOFWEEK ('2017-02-03 '); -> 3

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

The Code is as follows:	Copy code
Mysql> select WEEKDAY ('2017-10-04 22:23:00 '); -> 5

3. DAYOFMONTH (date)
Returns the date of a month in the range of 1 to 31.

The Code is as follows:	Copy code
Mysql> select DAYOFMONTH ('2017-02-03 '); -> 3

4. DAYOFYEAR (date)
Returns the number of days in a year from 1 to 366.

The Code is as follows:	Copy code
Mysql> select DAYOFYEAR ('2017-02-03 '); -> 34

5. MONTH (date)
Returns the month of date, ranging from 1 to 12.

The Code is as follows:	Copy code
Mysql> select MONTH ('2014-02-03 '); -> 2

6. DAYNAME (date)
Returns the week name of date.

The Code is as follows:	Copy code
Mysql> select DAYNAME ("1998-02-05 "); -> 'Thursday'

7. MONTHNAME (date)
Returns the month name of date.

The Code is as follows:	Copy code
Mysql> select MONTHNAME ("1998-02-05 "); -> 'February'

8. QUARTER (date)
Returns the quarter of a year from date, ranging from 1 to 4.

The Code is as follows:	Copy code
Mysql> select QUARTER ('98-04-01 '); -> 2

9. WEEK (date)
WEEK (date, first) has a single parameter for the place where Sunday is the first day of a WEEK, and returns the number of weeks of date, ranging from 0 to 52. The two parameters are allowed in WEEK () format. Specify whether the week starts on Sunday or Monday. If the second parameter is 0 and the week starts from Sunday, if the second parameter is 1,
Starting from Monday.

The Code is as follows:	Copy code
Mysql> select WEEK ('2017-02-20 '); -> 7 Mysql> select WEEK ('2017-02-20 ', 0 ); -> 7 Mysql> select WEEK ('2017-02-20 ', 1 ); -> 8

10, YEAR (date)
Returns the year of date, ranging from 1000 to 9999.

The Code is as follows:	Copy code
Mysql> select YEAR ('98-02-03 '); -> 1998

11. HOUR (time)
Returns the hour of time, ranging from 0 to 23.

The Code is as follows:	Copy code
Mysql> select HOUR ('10: 05: 03 '); -> 10

12. MINUTE (time)
Returns the minute of time, ranging from 0 to 59.

The Code is as follows:	Copy code
Mysql> select MINUTE ('98-02-03 10:05:03 '); -> 5

13. SECOND (time)
The number of seconds for the return time, ranging from 0 to 59.

The Code is as follows:	Copy code
Mysql> select SECOND ('10: 05: 03 '); -> 3

14. PERIOD_ADD (P, N)
Add N months to phase P (in the format of YYMM or YYYYMM ). Return Value in the format of YYYYMM. Note that the phase parameter P is not a date value.

The Code is as follows:	Copy code
Mysql> select PERIOD_ADD (9801,2 ); -> 199803

15. PERIOD_DIFF (P1, P2)
Returns the number of months between period P1 and P2. P1 and P2 should be in the format of YYMM or YYYYMM. Note that the period parameters P1 and P2 are not date values.

The Code is as follows:	Copy code
Mysql> select PERIOD_DIFF (9802,199703 ); -> 11

16,

The Code is as follows:	Copy code
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 date operations. For MySQL 3.22, they are new. ADDDATE () and SUBDATE () are synonyms of DATE_ADD () and DATE_SUB.
In MySQL 3.23, you can use + and-instead of DATE_ADD () and DATE_SUB (). (See the example) date is a specified start date.
DATETIME or DATE value. expr is an expression that specifies the interval value added to or subtracted from the start DATE. expr is a string. It can start with "-" to indicate the negative interval. Type is a keyword that specifies how the expression should be interpreted. The EXTRACT (type FROM date) function returns the "type" interval FROM the date.

The following table shows how the type and expr parameters are associated: The type value indicates the expected expr format.

SECOND SECONDS
MINUTE MINUTES
HOUR time HOURS
DAY DAYS
MONTH-MONTH MONTHS
YEAR YEARS
MINUTE_SECOND MINUTES and SECONDS "MINUTES: SECONDS"
HOUR_MINUTE hour and minute "HOURS: MINUTES"
DAY_HOUR and hour "days hours"
YEAR_MONTH and month "YEARS-MONTHS"
HOUR_SECOND hour, minute, "HOURS: MINUTES: SECONDS"
DAY_MINUTE day, hour, minute "days hours: MINUTES"
DAY_SECOND day, hour, minute, second "days hours: MINUTES: SECONDS"
MySQL allows any punctuation Separator in the expr format. It indicates that the recommended Delimiter is displayed. If the date parameter is a DATE value and your calculation only contains the YEAR, MONTH, and DAY sections (that is, there is no time section), the result is a DATE value. Otherwise, the result is a DATETIME value.

The Code is as follows:

Copy code

Mysql> SELECT "23:59:59" + INTERVAL 1 SECOND; -> 00:00:00 Mysql> select interval 1 DAY + "1997-12-31 "; -> 1998-01-01 Mysql> SELECT "1998-01-01"-INTERVAL 1 SECOND; -> 23:59:59 Mysql> SELECT DATE_ADD ("23:59:59 ", INTERVAL 1 SECOND ); -> 00:00:00 Mysql> SELECT DATE_ADD ("23:59:59", INTERVAL 1 DAY ); -> 23:59:59 Mysql> SELECT DATE_ADD ("23:59:59 ", INTERVAL "1:1" MINUTE_SECOND ); -> 00:01:00 Mysql> SELECT DATE_SUB ("00:00:00 ", INTERVAL "1" DAY_SECOND ); -> 1997-12-30 22:58:59 Mysql> SELECT DATE_ADD ("00:00:00 ", INTERVAL "-1 10" DAY_HOUR ); -> 1997-12-30 14:00:00 Mysql> SELECT DATE_SUB ("1998-01-02", INTERVAL 31 DAY ); -> 1997-12-02 Mysql> select extract (year from "maid "); -> 1999 Mysql> select extract (YEAR_MONTH FROM "01:02:03 "); -> 199907 Mysql> select extract (DAY_MINUTE FROM "01:02:03 "); -> 20102

If you specify an interval value that is too short (excluding the expected interval of the type keyword), MySQL assumes that you have saved the leftmost portion of the interval value. For example, if you specify a type of DAY_SECOND, the value of expr is expected to be one day, hour, minute, and second. If you specify a value like,
MySQL assumes that the days and hours are lost and the values represent minutes and seconds. In other words, "" DAY_SECOND is interpreted as it is equivalent to "" MINUTE_SECOND, this interpretation of MySQL's TIME value indicates that the elapsed TIME is not as a one-day TIME. If you use an incorrect date, the result is NULL. If you increase MONTH, YEAR_MONTH, or YEAR and the result date is greater than the maximum number of days of the new MONTH, the day is adjusted by the maximum number of days of the new moon.

The Code is as follows:

Copy code

Mysql> select DATE_ADD ('2017-01-30 ', Interval 1 month ); -> 1998-02-28 Note: In the previous example, the INTERVAL and type keywords are not case sensitive. TO_DAYS (date) Returns the number of days (from 0 years ). Mysql> select TO_DAYS (950501 ); -> 728779 Mysql> select TO_DAYS ('2017-10-07 '); -> 729669 17. TO_DAYS () is not intended to be used to use the value before the occurrence of the glipro calendar (1582. 18. FROM_DAYS (N) Returns a DATE value for the number of days N. Mysql> select FROM_DAYS (729669 ); -> '2017-10-07'

1 2