MySQL in the work of the accumulation

1. Convert a datetime column to a fixed format in a single piece of data

Date_format (create_date, '%y-%m-%d%T ') (Ps:create_date is a column name) results: 2016-10-17 10:37:03

Parameter description:%s,%s two digits in seconds (00, 01, ..., 59)

%i two digits in the form of points (00, 01, ..., 59)

%H two-digit hours, 24 hours (00,01, ..., 23)

%h,%I Two digits in the form of hours, 12 hours (01,02, ..., 12)

%k digital form of hours, 24 hours (0,1, ..., 23)

%l digital form of hours, 12 hours (1, 2, ..., 12)

%T 24-hour time form (hh:mm:s s)

%r 12-hour time format (hh:mm:ss AM or Hh:mm:ss PM)

%p AM or P M%W the name of each day of the week (Sunday, Monday, ..., Saturday)

%a the abbreviation of the name of each day of the week (Sun, Mon, ..., Sat)

%d two digits indicates the number of days in the month (00, 01, ..., 31)

%e number representation of days in the month (1, 2, ..., 31)

%d The English suffix indicates the number of days in the month (1st, 2nd, 3rd, ...). ）

%w number of days in the week (0 = Sunday, 1=monday, ..., 6=saturday)

%j three-digit number of days in the year (001, 002, ..., 366)

% U Week (0, 1, 52), where Sunday is the first day of the week

%u weeks (0, 1, 52), where Monday is the first day of the week

%M month name (January, February, ..., December)

%b Abbreviated month name (January, February, ..., December)

%m Two digit month (01, 02, ..., 12)

The month of%c numbers (1, 2, ..., 12)

%Y Four-digit year

%y two-digit year percent direct value "%"

2. Date function

　　

PS: specific introduction See http://www.w3school.com.cn/sql/sql_dates.asp

3, query 24 hours of data

Create_date is the time to create data, datetime type

SELECT Id,date_format (create_date, '%y-%m-%d%T ') as ctime,temperature from ' Ch_temperature ' where create_date > DATE_ SUB (now (), INTERVAL 1 day)

MySQL in the work of the accumulation