CODEWITHSUNDEEP
mysqlsqldatabasegroup-byrelational-database
miiiii
miiiii

Reputation: 1610

Group by fixed interval range

I have time stored in unix format int. I want to group the result by the intervals of 1day i.e 86400 seconds.

sample data

timestamp  | state
+++++++++++++++++++
1540427604 | ABC
1540770300 | PQR
1540770050 | PQR
1540751300 | ABC

I want to group the count of state by period of each 86400, i.e if someone asked from 1540425600 (i.e 25 OCT 2018) to 1540771200 (i.e 29 OCT 2018), then output should be

range                    | state  | count
++++++++++++++++++++++++++++++++++++++++++
1540425600 - 1540512000  |   ABC  | 1
1540684800 - 1540771200  |   ABC  | 1
1540684800 - 1540771200  |   PQR  | 2

I've tried this so many things but this one is what I think more close to..

select 
    concat(86400*floor(timestamp /86400), '-', 86400*floor(timestamp/86400) + 86400) as `range` , count(state)
    from MT 
    where timestamp between MINDATE AND MAXDATE
    group by `range` order by `range`;

But the output I'm getting is not starting with the MINDATE passed or in multiple of MINDATE... but it is in range of 86400. I want the range should always start with mMINDATE if such record exists OR in muliple of MINDATE * 86400

Please help.

Upvotes: 1

Views: 118

Answers (1)

fancyPants
fancyPants

Reputation: 51868

This here does the job:

select
concat(min(floor(timestamp / 86400) * 86400), ' - ', max(floor(timestamp / 86400) * 86400) + 86400) as `range`
, state
, count(*)
from t
group by floor(timestamp / 86400), state
order by `range`

Upvotes: 1

Related Questions