[MySQL] Statistics by date (day before, week, someday)

Source: Internet
Author: User

In MySQL database, you often encounter the content of the day of statistics.
For example, in the user table, the Date field is: Log_time
Statistics Day

The SQL statement is:

SELECT * from user where date (log_time) = Curdate ();

Curdate () indicates the date of the day
Statistics day before

If the data for the previous day is indicated, you cannot use Curdate ()-1, because the Curdate ()-1st period is not the month-end date for the last one months when the date is the beginning of the month.

For example: Today is June 1, theoretically curdate ()-1 is May 31, but Curdate ()-1 gets not May 31, but 0 days. Then the date of the day before the statistics can not use Curdate ()-1, MySQL database has a new method to count the data the previous day.

Statistics log SQL statements for the day before:

SELECT * from beans where date (log_time) = Date_sub (Curdate (), Interval 1 day);

In parentheses, the day before today, if the number of days before the statistics, the parentheses in the ' 1 ' is changed to the corresponding days. If you want to count months or years, change the day directly to month or year
Statistics this week

Requirements: Statistics starting from yesterday 7 days before the diary including yesterday

SELECT * from user-where date (log_time) >= date_sub (Curdate (), Interval 7 day)
and date (log_time) <= date_sub (Curdate (), Interval 1 day)

On the Internet to find the use of week statistics for a week, can only be counted to 5 days of information, not meet the requirements, so use this method.
Count One day

Statistics history a day's log, replacing Curdate () in Date_sub (Curdate (), Interval 0 day) function with a date
For example: to count the 2012-05-25 date information
Date_sub (' 2012-05-25 ', Interval 0 day)
An example of a date_sub () function:

Today is May 20, 2013.
Date_sub (' 2012-05-25 ', Interval 1 day) says 2012-05-24
Date_sub (' 2012-05-25 ', Interval 0 day) says 2012-05-25
Date_sub (' 2012-05-25 ', interval-1 day) says 2012-05-26
Date_sub (' 2012-05-31 ', interval-1 day) says 2012-06-01
Date_sub (Curdate (), Interval 1 day) represents 2013-05-19
Date_sub (Curdate (), Interval-1 day) represents 2013-05-21
Date_sub (Curdate (), Interval 1 month) represents 2013-04-20
Date_sub (Curdate (), Interval-1 month) represents 2013-06-20
Date_sub (Curdate (), Interval 1 year) represents 2012-05-20
Date_sub (Curdate (), Interval-1 year) represents 2014-05-20

[MySQL] Statistics by date (day before, week, someday)

Related Article

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.