[MySQL] query for a period of time record

Tag: des io works with AR sp art on amp

Record within 24 hours (i.e. 86,400 seconds)

$sql = "SELECT Video_id,count (ID) as n from ' Rec_down ' WHERE Unix_timestamp (now ())-unix_timestamp (add_time) <=86400 Group by video_id ORDER by n DESC ";
$sql = "Select A.ID,A.TITLE,B.N from Video_info A, (". $sql. ") b where a.id=b.video_id ORDER by n desc limit 20 ";

N Days of recording

WHERE To_days (now ())-To_days (Time field) <= N

Today's record

Where date (Time field) =date (now ())

Or

Where To_days (Time field) = To_days (now ());

Query one week:

SELECT * FROM table where Date_sub (Curdate (), INTERVAL 7 day) <= DATE (column_time);

Query one months:

SELECT * FROM table where Date_sub (Curdate (), INTERVAL INTERVAL 1 MONTH) <= DATE (column_time);

The 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; 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-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.

[MySQL] query for a period of time record