MySQL query SQL statements by Time Summary (1/2)

Description: There is a member table, there is a birthday field, the value is ' YYYY-MM-DD ' format, now to query a time period of the birthday of members, such as ' 06-03 ' to ' 07-08 ' in this time period of all birthday members.

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 ';

Description: The commonly used time date processing function, the above is mainly Date_format () the application of this function.

1, DayOfWeek (date)
returns the week index of date (1= Sunday, 2 = Monday, ...). 7= Saturday). These index values correspond to ODBC standards.

The code is as follows	Copy Code
Mysql> Select DayOfWeek (' 1998-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 (' 1997-10-04 22:23:00 '); -> 5

3, DayOfMonth (date)
returns the date in the month of date, in the range 1 through 31.

The code is as follows	Copy Code
Mysql> Select DayOfMonth (' 1998-02-03 '); -> 3

4, DayOfYear (date)
returns the number of days in a year, in the range of 1 to 366.

The code is as follows	Copy Code
Mysql> Select DayOfYear (' 1998-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 (' 1998-02-03 '); -> 2

6, Dayname (date)
returns the name of the week of date.

The code is as follows	Copy Code
Mysql> Select Dayname ("1998-02-05"); -> ' Thursday '

7, MonthName (date)
returns the month name of the date.

The code is as follows	Copy Code
Mysql> Select MonthName ("1998-02-05"); -> ' February '

8, Quarter (date)
returns the quarter of date one year, 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) for Sunday is the first day of the week, there is a single parameter that returns the week number of date, ranging from 0 to 52. 2 parameter Forms week () allowed. Do you specify whether the week starts in Sunday or Monday. If the second argument is 0, the week begins in Sunday, and if the second argument is 1,
Starting from Monday.

The code is as follows	Copy Code
Mysql> Select WEEK (' 1998-02-20 '); -> 7 Mysql> Select WEEK (' 1998-02-20 ', 0); -> 7 Mysql> Select WEEK (' 1998-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 minutes 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 to 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)
increase n months to phase p (in format Yymm or yyyymm). Returns a value in YYYYMM format. 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 periods P1 and P2, P1 and P2 should be in a format yymm or yyyymm. Note that the time parameter 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 for Date_add () and Date_sub ().
In MySQL 3.23, you can use + and-instead of Date_add () and Date_sub (). (see example) date is a specified start date
datetime or Date value, expr is an expression that specifies an interval value added to the start date or subtracted from the start date, and expr is a string; it can represent a negative interval with a "-" start. Type is a keyword that indicates how an expression should be interpreted. The EXTRACT (type from date) function returns the "type" interval from the date.

The following table shows how type and expr parameters are associated: the desired expr format for type value meaning

SECOND seconds SECONDS
MINUTE minutes MINUTES
HOUR Time HOURS
Day days
MONTH Month MONTHS
Year YEARS
Minute_second minute and second "Minutes:seconds"
Hour_minute hour and Minute "hours:minutes"
Day_hour Day and Hour "days HOURS"
Year_month year and month "Years-months"
Hour_second hours, minutes, "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. Indicates that the suggested separator is displayed. If the date parameter is a date value and your calculation contains only the year, month, and day portions (that is, there is no time part), the result is a date value. Otherwise, the result is a datetime value.

The code is as follows

Copy Code

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 Mysql> SELECT EXTRACT (year from "1999-07-02"); -> 1999 Mysql> SELECT EXTRACT (year_month from "1999-07-02 01:02:03"); -> 199907 Mysql> SELECT EXTRACT (day_minute from "1999-07-02 01:02:03"); -> 20102

If you specify an interval value that is too short (excluding the desired interval for the type keyword), MySQL assumes that you have omitted the leftmost portion of the interval value. For example, if you specify that a type is Day_second, the value expr is expected to have the day, hour, minute, and second portions. If you specify a value like "1:10",
MySQL assumes that the days and hours are partly missing and the values represent minutes and seconds. In other words, the "1:10" Day_second is interpreted as equivalent to the "1:10" Minute_second, which is two justified by the way that MySQL interprets the time value as a passing period rather than as a day's time. If you use a date that is not exactly correct, the result is null. If you add month, year_month or year and the result date is greater than the maximum number of days for the new month, the day is adjusted with the largest day in the crescent moon.

The code is as follows

Copy Code

Mysql> Select Date_add (' 1998-01-30 ', Interval 1 month); -> 1998-02-28 Note that from the previous example the words interval and type keywords are not case-sensitive. To_days (date) Gives a date dated, returning a number of days (from 0 years). Mysql> Select To_days (950501); -> 728779 Mysql> Select To_days (' 1997-10-07 '); -> 729669 17, To_days () is not intended to use the Gregory (1582) before the occurrence of the value. 18, From_days (N) Gives a number of days N, returning a date value. Mysql> Select From_days (729669); -> ' 1997-10-07 '

Home 1 2 last page