By week select Date_format (create_time, '%y%u ') weeks,count (Caseid) count from Tc_case Group by weeks; Monthly Select Date_format (create_time, '%y%m ') months,count (Caseid) count from Tc_case Group by months; by day Select Date_format (create_time, '%y%m%d ') days,count (Caseid) count from Tc_case Group by days; by Hour select date_format (create_time, '%y%m%d%h ') hours,count (Caseid) count from Tc_case group by hours; date_format method Description Format: date_format (Date,format) formats the DATE value according to the format string. The following modifiers can be used in the format string:%M month name (January ... December)%W week name (Sunday ... Saturday)%d The date of the month with English prefixes (1st, 2nd, 3rd, etc.). )%Y year, number, 4-digit%Y year, number, 2-bit%a abbreviated weekday name (Sun ... Sat)%d days in the month, number (00 ... %e number of days in the month, number (0 ... %m months, numbers (01 ... %c month, number (1 ... %b Abbreviated month name (Jan ... DEC)%j Days of the year (001 ... 366)%H hours (00 ... %k hours (0 ...) %h hours (01 ...) %I) Hours (01 ... %l hours (1 ...) %i minutes, Numbers (00 ...) %r time, 12 hours (Hh:mm:ss [ap]m)%T time, 24 hours (hh:mm:ss)%s seconds (00 ... ()%s seconds (00 ... %p am or pm%w one days of the week (0=sunday ... 6=saturday)%u week (0 ... 52), here Sunday is the starThe first day of the period%u week (0 ... 52), here Monday is the first day of the week with a text "%". Aggregated by year, statistics: select SUM (Mymoney) as Totalmoney, COUNT (*) as sheets from MyTable Group by Date_format (Col, '%Y '), monthly summary, statistics: s Elect sum (Mymoney) as Totalmoney, COUNT (*) as sheets from MyTable Group by Date_format (Col, '%y-%m '), quarterly Summary, Statistics: select SUM (M Ymoney) as Totalmoney,count (*) as sheets from MyTable Group by concat (Date_format (col, '%Y '), Floor ((Date_format (col, '%m ' ) (+2)/3); Select sum (Mymoney) as Totalmoney,count (*) as sheets from MyTable Group by concat (Date_format (col, '%Y '), Floor (( Date_format (col, '%m ') +2)/3); by hour: select SUM (Mymoney) as Totalmoney,count (*) as sheets from MyTable Group by Date_format ( Col, '%y-%m-%d%H '); Querying data for the current year: SELECT * FROM MyTable (From_unixtime (my_time)) = years (Curdate ()) query data with quarter number: Select ID, Quarter (from_unixtime (my_time)) from mytable; querying data for the quarter: SELECT * from MyTable WHERE quarter (From_unixtime (my_time)) = Quarter (Curdate ()); This month statistics: SELECT * FROM MyTable where month (my_time1) = month (Curdate ()) and year (my_time2) =Year (Curdate ()) Week statistics: SELECT * FROM MyTable where month (my_time1) = month (Curdate ()) and week (my_time2) = Week (Curdate ()) n days Internal record: WHERE To_days (now ())-to_days (Time field) <=n
MySQL by week, by month, by day, by hour group statistics
