MySQL is counted by date time period (the previous day, this week, a certain period of time)

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 = 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 where = 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 >= 7  Day )    and<=  1Day)  

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'0Day)

An example of a date_sub () function:

Today is May 20, 2013.

Date_sub ('2012-05-25', Interval1  Day) indicates -- to- -Date_sub ('2012-05-25', Interval0  Day) indicates -- to- -Date_sub ('2012-05-25', Interval-1  Day) indicates -- to- -Date_sub ('2012-05-31', Interval-1  Day) indicates -- .- ondate_sub (Curdate (), Interval1  Day) indicates -- to- +date_sub (Curdate (), Interval-1  Day) indicates -- to- +date_sub (Curdate (), Interval1 Month) indicates --Geneva- -date_sub (Curdate (), Interval-1 Month) indicates -- .- -date_sub (Curdate (), Interval1  Year) indicates -- to- -date_sub (Curdate (), Interval-1  Year) indicates the- to- -

Statistics by time period:

The Date_format function is used here, and the function is very versatile, as in the following example:

Select *  from User whereDate_format (Log_time,'%H')>= ' -'  andDate_format (Log_time,'%H')< ' +'  andDate_format (Log_time,'%Y')=' the';

I don't need to say that, as everyone knows, it's about 18 to 19 points, and it's 2014 data.

Date_format can be used in the following formats:

Format	Describe
%a	Abbreviated Week name
%b	Abbreviated month name
%c	Month, value
%d	Day of the month with English prefixes
%d	Day of the month, value (00-31)
%e	Day of the month, value (0-31)
%f	Subtle
%H	Hours (00-23)
%h	Hours (01-12)
%I	Hours (01-12)
%i	Minutes, value (00-59)
%j	Days of the Year (001-366)
%k	Hours (0-23)
%l	Hours (1-12)
%M	Month Name
%m	month, value (00-12)
%p	AM or PM
%r	Time, 12-hour (Hh:mm:ss AM or PM)
%s	Seconds (00-59)
%s	Seconds (00-59)
%T	Time, 24-hour (HH:MM:SS)
%u	Week (00-53) Sunday is the first day of the week
%u	Week (00-53) Monday is the first day of the week
%V	Week (01-53) Sunday is the first day of the week, with%x
%v	Week (01-53) Monday is the first day of the week, with%x
%W	Week name
%w	Day of the week (0= Sunday, 6 = Saturday)
%x	year, of which Sunday was the first day of the week, 4-bit, with%V used
%x	year, of which Monday was the first day of the week, 4-bit, with%v used
%Y	Year, 4 guests
%y	Year, 2 guests

MySQL is counted by date time period (the previous day, this week, a certain period of time)