SQL Group By Month with a timestamp field

Posted one year ago

In order to convert a timestamp to a date managed column, you need to wrap the column with “FROM_UNIXTIME()” see below


SELECT  
	YEAR(FROM_UNIXTIME(join_date)) AS YearDate,
	MONTH(FROM_UNIXTIME(join_date)) AS MonthDate,
	count(*)
FROM members 
	GROUP BY 
	YEAR(FROM_UNIXTIME(join_date)),
	MONTH(FROM_UNIXTIME(join_date))

We can trim this down a little further by formatting our query… note the “GROUP BY” is now filtering on column 1,2 if we removed 2 it would filter just on the first column and likewise if we used additional columns we would filter on say 4 and 5 for example.


SELECT  
	YEAR(FROM_UNIXTIME(join_date)) AS YearDate,
	MONTH(FROM_UNIXTIME(join_date)) AS MonthDate,
	count(*)
FROM members 
	GROUP BY 1,2