Mysql statistics function DATE_FORMAT () function _ MySQL by month/week

Source: Internet
Author: User
Define and use the DATE_FORMAT () function to display date and time data in different formats. Syntax: DATE_FORMAT (date, format) date is a valid date. Format specifies the output format of the date and time. The following statements can be used: & definitions and usage
The DATE_FORMAT () function is used to display date/time data in different formats.
Syntax
DATE_FORMAT (date, format)
The date parameter is a valid date. Format specifies the output format of the date/time.
The following formats are available:
Format Description
% A abbreviated name for a week
% B abbreviated month name
% C month, numeric value
% D the day of the month with an English prefix
Day of % d Month, value (00-31)
% E Day (0-31)
% F microseconds
% H Hour (00-23)
% H Hour (01-12)
% I hour (01-12)
% I minutes, numeric value (00-59)
% J-Day (001-366)
% K hour (0-23)
% L hour (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, used with % X
% V week (01-53) Monday is the first day of the week, used with % x
% W week name
% W Day (0 = Sunday, 6 = Saturday)
% X, where Sunday is the first day of the week, 4 digits, used with % V
% X, where Monday is the first day of the week, 4 digits, used with % v
% Y year, 4 digits
% Y years, two places
Instance
The following script uses the DATE_FORMAT () function to display different formats. NOW () is used to obtain the current date/time:
DATE_FORMAT (NOW (), '% B % d % Y % h: % I % p') DATE_FORMAT (NOW (),' % m-% d-% Y ') DATE_FORMAT (NOW (), '% d % B % Y') DATE_FORMAT (NOW (),' % d % B % y % T: % f ')
The result is similar:
Dec 29 2008 16:25:46 PM 12-29-2008 29 Dec 08 29 Dec 2008. 635
Instance

SELECT DATE_FORMAT ('Order creation time', '% u') as week,
Sum ('total') as money from corder
Where DATE_FORMAT ('Order creation time', '% Y') = 2012
Group by week
Result:
Week money
13 854314.83
14 5177385.26
15 4870063.87
16 4529686.53
17 3678584.15

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.