How does mysql query records for a period of time-1024programmer

Mysql query method for a period of time records: 1. Query records within N days, the code is [WHERE TO_DAYS(NOW()) – TO_DAYS(time field) <= N]; 2. Query today’s records, code It is [where date (time field) = date (now ())].

More related free learning recommendation: mysql tutorial (video)

mysql query section Time recording method:

Record within 24 hours (ie 86400 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";

Record within N days

WHERE TO_DAYS(NOW()) - TO_DAYS(time field) <= N

Today’s record

where date(time field)=date(now())

where to_days(time field) = to_days(now());

Query a week:

select * from table where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);

Query One month:

select * from table where DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= date(column_time);
  pre>The query selects all records with date_col values within the last 30 days.  
mysql> SELECT something FROM tbl_name
             WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30; //It's really convenient, I used to write it by myself, but I didn't know it, so it failed.
DAYOFWEEK(date)   
Returns the day of the week index of date (1 = Sunday, 2 = Monday, ... 7 = Saturday).  Indexed values conform to the ODBC standard.  
mysql> SELECT DAYOFWEEK('1998-02-03');
          -> 3
WEEKDAY(date)  
Returns the weekday 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 of the month in the range of 1 to 31: 
mysql> SELECT DAYOFMONTH('1998-02-03');
          -> 3
DAYOFYEAR(date) 
The 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 date: 
mysql> SELECT MONTHNAME("1998-02-05");
          -> 'February'
QUARTER(date) 
Returns the quarter of the year for date, from 1 to 4: 
mysql> SELECT QUARTER('98-04-01');
          -> 2
WEEK(date) 
WEEK(date,first) 
for Sunday  In the case of the first day of the week, if the function is called with only one argument, the return date is the week of the year, and the return value ranges from 0 to 53 (yes, there may be a start of week 53).  The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday, and whether the return value is 0-53 or 1-52.  Here's a table showing how the second parameter works:
Value meaning
0 The week starts on Sunday, and the return value ranges from 0-53
  1 The week starts with Monday, the return value range is 0-53
2 The week starts with Sunday, the return value range is 1-53
3 The week starts with Monday, the return value  The 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, MySQL will return 0 when you don't use 2 or 3 as optional parameters:
mysql  > SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
          -> 2000, 0
 mysql> SELECT WEEK('2000-01-01',2);
          -> 52
You could argue that MySQL should return 52 for the WEEK() function when the given date value is actually part of week 52 in 1999.  We decided to return 0 , yesBecause we want the function to return "the week in the specified year".  This makes usage of the WEEK() function reliable when used in conjunction with other functions that extract the day-of-month value from a date value.  If you prefer to get the correct year-week value, then you should use 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, ranging from 1000 to 9999:
mysql> SELECT YEAR('98-02-03');
          -> 1998
YEARWEEK(date)
YEARWEEK(date,first)
Return a  Which week of the year is the date value.  The form and function of the second parameter are 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 returned year value may be inconsistent 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 the return value (0) of the WEEK() function, WEEK() returns the week according to the given year context  value.  
HOUR(time)
Returns the hour value of time, 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 second value of time, ranging from 0 to 59:
mysql> SELECT SECOND('10:05:03');
          -> 3
PERIOD_ADD(P,N)
Add N months to period P (in YYMM or YYYYMM format).  Returns the 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 periods P1 and P2.  P1 and P2 should be specified in 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)
  p>
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 datetime field, you can use + and - instead of DATE_ADD() and DATE_SUB() (examples below).  The parameter date is a DATETIME or DATE value specifying the beginning of a date.  expr is an expression specifying whether to add or subtract the interval value from the start date.  expr is a string; it can be preceded by a "-" to indicate a negative interval value.  type is a keyword that indicates in what format the expression is to be interpreted.  
The above is the detailed content of how mysql queries records for a period of time. For more information, please pay attention to other related articles on 1024programmer.com!