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
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
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:
||Abbreviated Week name
||Abbreviated month name
||Day of the month with English prefixes
||Day of the month, value (00-31)
||Day of the month, value (0-31)
||Minutes, value (00-59)
||Days of the Year (001-366)
||month, value (00-12)
||AM or PM
||Time, 12-hour (Hh:mm:ss AM or PM)
||Time, 24-hour (HH:MM:SS)
||Week (00-53) Sunday is the first day of the week
||Week (00-53) Monday is the first day of the week
||Week (01-53) Sunday is the first day of the week, with%x
||Week (01-53) Monday is the first day of the week, with%x
||Day of the week (0= Sunday, 6 = Saturday)
||year, of which Sunday was the first day of the week, 4-bit, with%V used
||year, of which Monday was the first day of the week, 4-bit, with%v used
||Year, 4 guests
||Year, 2 guests
MySQL is counted by date time period (the previous day, this week, a certain period of time)