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

Source: Internet
Author: User
Tags month name

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)

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.