MySQL gets the data in a time range to_days (date) function with the now () function

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> SELECTTO_DAYS(‘2009-08-07‘);

2	+-----------------------+

3	| TO_DAYS(‘2009-08-07‘) |

4	+-----------------------+

5	|                733991 |

6	+-----------------------+

7	1 row inset

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> SELECTTO_DAYS(‘2009-08-07‘),TO_DAYS(‘09-08-07‘);

2	+-----------------------+---------------------+

3	| TO_DAYS(‘2009-08-07‘) | TO_DAYS(‘09-08-07‘) |

4	+-----------------------+---------------------+

5	|                733991 |              733991 |

6	+-----------------------+---------------------+

7	1 row inset

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.

1	mysql> select(now());

2	+---------------------+

3	| (now())             |

4	+---------------------+

5	| 2010-08-26 21:18:44 |

6	+---------------------+

7	1 row inset

To_days (Current_date) can also implement the above functions.

1	mysql> selectto_days(current_date);

2	+-----------------------+

3	| to_days(current_date) |

4	+-----------------------+

5	|                734375 |

6	+-----------------------+

7	1 row inset

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 desclimit 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