MySQL 按 Unix 时间戳字段的月份分组

group by month of unix timestamp field

提问by scarhand

I'm trying to get my code to output in the following format:

我试图让我的代码以以下格式输出：

january 2012 - 34
february 2012 - 23

where 34 and 23 would be a count of the total rows that fall within that month that have the id_dealership of 7. I need this to output all data for every month that an assignment was ever made.

其中 34 和 23 将是该月内 id_dealership 为 7 的总行数。我需要它来输出每个月的所有数据。

The assignmentstable structure is as follows:

该assignments表结构如下：

id_dealer (int)
date_assigned (int)

I've tried this but it does not work at all:

我试过这个，但它根本不起作用：

SELECT MONTH(date_assigned), YEAR(date_assigned), COUNT(*)
FROM assignments
GROUP BY MONTH(date_assigned), YEAR(date_assigned)

回答by Eugen Rieck

SELECT 
  MONTH(FROM_UNIXTIME(date_assigned)), 
  YEAR(FROM_UNIXTIME(date_assigned)), 
  COUNT(*)
FROM assignments
GROUP BY 
  MONTH(FROM_UNIXTIME(date_assigned)), 
  YEAR(FROM_UNIXTIME(date_assigned))

回答by giorashc

Your date_assigned column should be of type DATE. AFAIK MONTH works on date columns and if you want the month name from a DATE column use : MONTHNAME(date_assigned)

您的 date_assigned 列的类型应为 DATE。AFAIK MONTH 适用于日期列，如果您想要 DATE 列中的月份名称，请使用：MONTHNAME(date_assigned)

回答by Sergeev Alexey

try this query

试试这个查询

SELECT 
  MONTH(FROM_UNIXTIME(date_assigned)), 
  YEAR(FROM_UNIXTIME(date_assigned)), 
  COUNT(*)
FROM assignments
GROUP BY 1,2