Mysql groups the field time by day, month, or year.

Mysql groups the field time by day, month, or year.

Assume that the table has a time field in Unix timestamp format. You need to calculate the number of records per day based on this field.

Method:

Use the convert function convert (char (10), time, 120) as time, and then group by time.

Principle: The convert function converts the timestamp to the ODBC Standard Time (specified by the 120 parameter), takes the first 10 digits, and obtains the complete date (excluding the time, minute, and second), and then groups it.

The duration of the first parameter of the convert () method is the same as that of the yearly grouping method.

Mysql time field query by week grouping

I don't quite understand what you mean. I think so. It is calculated by calendar week. There are 52 weeks in a year.

Select DATEPART (YEAR, [time field]) * 100 + DATEPART (MONTH, [time field]), datepart (week, [time field]), SUM ([quantity]) from table name
Group by DATEPART (YEAR, [time field]) * 100 + DATEPART (MONTH, [time field]), datepart (week, [time field])

Teach you how to query mysql instances by time segment

Use the DATE_FORMAT function.
After formatting, The result contains only four parts: year, month, and day.
Then you can Group.

The following is an example and description of the DATE_FORMAT function.

Mysql> SELECT
-> DATE_FORMAT (NOW (), '% Y ~ % M ~ % D % k. % I. % s');
+ --------------------- +
| A |
+ --------------------- +
| 2010 ~ 10 ~ 22 2.1655.09 |
+ --------------------- +
1 row in set (0.00 sec)

% W name of the Week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the Week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H hour (00 ...... 23)
% K hour (0 ...... 23)
% H hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, number (00 ...... 59)
% R time, 12 hours (hh: mm: ss [AP] M)
% T time, 24 hours (hh: mm: ss)
% S seconds (00 ...... 59)
% S seconds (00 ...... 59)
% P AM or PM
% W days in a week (0 = Sunday ...... 6 = Saturday)
% U Week (0 ...... 52). Sunday is the first day of the week.
% U Week (0 ...... 52) Monday is the first day of the week.
% A text "% ".

All other characters are not interpreted and copied to the result.