With such a requirement, we need to get all the data records for a certain period of time, such as news in the last one months, and then sort them in this data collection to see which news has the highest click rate. Now the question is, how to get that time period. MySQL has some functions to help us, first look at these functions.
To_days (date) function
Given a date, returns a number of days, starting from year 0.
For example, the following:
1 |
mysql> SELECT TO_DAYS( ‘2009-08-07‘ ); |
2 |
+ -----------------------+ |
3 |
| TO_DAYS( ‘2009-08-07‘ ) | |
4 |
+ -----------------------+ |
6 |
+ -----------------------+ |
To_days () is not used for values before the Gregorian calendar (1582), because the lost date is not taken into account when the calendars are changed.
Keep in mind that the rules in MySQL date and time type convert the two-digit year value in a date to four-bit. For example, ' 2010-08-07′ and ' 10-08-07′ are considered the same date:
1 |
mysql> SELECT TO_DAYS( ‘2009-08-07‘ ),TO_DAYS( ‘09-08-07‘ ); |
2 |
+ -----------------------+---------------------+ |
3 |
| TO_DAYS( ‘2009-08-07‘ ) | TO_DAYS( ‘09-08-07‘ ) | |
4 |
+ -----------------------+---------------------+ |
6 |
+ -----------------------+---------------------+ |
The result of this function is not reliable for dates prior to 1582 (perhaps in other regions for the next year).
Now () function
The now () function returns the current date and time.
2 |
+ ---------------------+ |
4 |
+ ---------------------+ |
5 |
| 2010-08-26 21:18:44 | |
6 |
+ ---------------------+ |
To_days (Current_date) can also implement the above functions.
1 |
mysql> select to_days( current_date ); |
2 |
+ -----------------------+ |
3 |
| to_days( current_date ) | |
4 |
+ -----------------------+ |
6 |
+ -----------------------+ |
Results
Now we can draw the answer to the question:
1 |
select * from news where to_days(now())-to_days(newsdate) < 30 order by view desc limit 0, 10 |
You can get the highest number of 10 records in one months.
Go to (http://www.nowamagic.net/librarys/veda/detail/415)
(go) MySQL gets the data in a time range to_days (date) function and now () function