SQL query to count the number of rows per month with data in Unix TimeStamp format

Question

In my Mysql database I have a column that stores the date values in Unix TimeStamp (type / BigInt, milliseconds). I need to write a query that allows me to count the number of lines for each month (regardless of the year).

Table:

    +-------+----------------+
    |   id  |    Startdata   |
    +-------+----------------+
    |     1 |  1580841222491 |  
    |     2 |  1580841235885 |  
    |     3 |  1580841235872 |  
    |     4 |  1580843242865 |  
    |     5 |  1580841134857 | 
    |     6 |  1580841334855 | 
    |     7 |  1580842252695 | 
    |     8 |  1580844236845 | 
       ...         ... 
    +-------+----------------+

Desired return:

+-------+-------+
| count | month |
+-------+-------+
|     4 |     1 |  
|     1 |     2 |  
|     6 |     3 |  
|    51 |     4 |  
|    21 |     5 | 
|    29 |     6 | 
|    41 |     7 | 
|    18 |     8 | 
|    21 |     9 | 
|    11 |    10 | 
|    38 |    11 |
|    23 |    12 |
+-------+-------+

function UNIX_TIMESTAMP does not work

Rajat · Accepted Answer · 2020-09-15 19:26:44Z

3

from_unixtime allows you to specify the format of the output as well. In your case, %m is all you need.

select from_unixtime(Startdata/1000,"%m"), count(*)
from t
group by from_unixtime(Startdata/1000,"%m")

answered Sep 15, 2020 at 19:26

Rajat

5,8733 gold badges14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fahmi · Accepted Answer · 2020-09-15 19:10:15Z

2

You can try the below -

select month(date(from_unixtime(floor(Startdata/1000)))) as month_value,count(*) as cnt
from tablename
group by month(date(from_unixtime(floor(Startdata/1000))))

Note: If your MySql version is 8+ then you don't need the floor function

answered Sep 15, 2020 at 19:10

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

2 Comments

Slava Rozhnev Over a year ago

I tested it on MySQL 5.7, it works without FLOOR too. phpize.online/…

Fahmi Over a year ago

@SlavaRozhnev, that's great!!

Slava Rozhnev · Accepted Answer · 2020-09-15 19:15:11Z

1

The Startdata column in your table in milliseconds, so you need divide it by 1000 to convert into seconds. So query will be:

SELECT 
    COUNT(*) AS `count`, 
    MONTH(FROM_UNIXTIME(Startdata/1000)) AS `month`
FROM `mytable`
GROUP BY `month`;

Live example here

answered Sep 15, 2020 at 19:15

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Collectives™ on Stack Overflow

SQL query to count the number of rows per month with data in Unix TimeStamp format

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related