How to query records for a period of time in mysql

Mysql method of querying records for a period of time: 1. Query records within N days, the code is [WHERE TO_DAYS(NOW()) - TO_DAYS (time field)

More related free learning recommendations: mysql tutorial(video)

mysql method of querying records for a period of time:

Records within 24 hours (i.e. 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";

Records within N days

WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段) <= N

Today’s record

where date(时间字段)=date(now())

or

where to_days(时间字段) = to_days(now());

Query for 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);

The query selects all records with date_col value within the last 30 days.

mysql> SELECT something FROM tbl_name
            WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;    //真方便,以前都是自己写的,竟然不知道有这,失败.

DAYOFWEEK(date)

Returns the week index of date (1 = Sunday, 2 = Monday, ... 7 = Saturday). Index values ​​conform to ODBC standards.

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)

Return date is 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)

Return date The month in , ranging from 1 to 12:

mysql> SELECT MONTH(’1998-02-03’);
         -> 2

DAYNAME(date)

Returns the day of 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 date in the quarter of the year, The range is 1 to 4:

mysql> SELECT QUARTER(’98-04-01’);
         -> 2

WEEK(date)

WEEK(date,first)

For Sunday it is In the case of the first day of the week, if the function is called with only one argument, the returned date is the week of the year, and the return value ranges from 0 to 53 (yes, there may be a beginning of the 53rd week). 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 range is 0-53

1 The week starts Starting on Monday, the return value range is 0-53

2. The week starting on Sunday, the return value range is 1-53

3. The week starting on 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, MySQL will return 0 when you do not use 2 or 3 as the optional parameter:

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 the given date value When it is actually part of the 52nd week of 1999, MySQL's WEEK() function should return 52. We decided to return 0 because we want the function to return "the week in the specified year." This makes the use of the WEEK() function reliable when used in conjunction with other functions that extract the month and day values ​​from date values. If you prefer to get the appropriate 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)

Returns which week of year a date value is. The form and function of the second parameter are completely consistent with the second parameter of WEEK(). Note that for a given date parameter that is the first or last week of the year, the returned year value 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 the value returned by the WEEK() function (0), WEEK() returns the week value according to the given year context.

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 (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,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)

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() (example below). The date parameter is a DATETIME or DATE value specifying the beginning of a date. expr is an expression that specifies whether to add or subtract the interval value from the start date. expr is a string; it can be preceded by a "-" to represent a negative interval value. type is a keyword that indicates the format in which the expression is interpreted.

The above is the detailed content of How to query records for a period of time in mysql. For more information, please follow other related articles on the PHP Chinese website!