Select all records based on month

[honkmaster]

Hi, I'm trying to Select all records in my database based on month and count them. The dates are store as unix timestamp as below. So  if I wanted to return all records for November 2019 the result would be 5 based on below.

I have started the query as below but I'm not sure if DATEPART() is there correct way to finish.

SELECT COUNT(Target) FROM production WHERE DATE(FROM_UNIXTIME(production.Target)) = 

Target
----------
1574701200
1574701200
1574701200
1574701200
1574701200
1572436800
1572436800
1572436800

 

Cheers Chris (PHP/SQL Novice)

[Barand]

mysql> SELECT monthname(from_unixtime(target)) as Month
    ->      , COUNT(*) as Total
    -> FROM production
    -> GROUP BY month(from_unixtime(target));
+----------+-------+
| Month    | Total |
+----------+-------+
| October  |     3 |
| November |     5 |
+----------+-------+

 

[honkmaster]

Thank you so much, that was great and worked perfectly, is there any way of getting the Year in there as well? Cheers Chris

[Barand]

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_year