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)