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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.