CODEWITHSUNDEEP
sqlitegroup-by
SevenOfNine
SevenOfNine

Reputation: 648

group by time interval

Assume I have a table like

enter image description here

how can I create a table like

enter image description here

where the groups are created of timeintervals with the length of 1 second.

Thank you in advance!

Upvotes: 3

Views: 530

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

Here is an idea, but you need a table of numbers

select (m.startts + n.n - 1) as starttime,
       (m.startts + n.n) as enddtime,
       sum(case when vehicle_type = 'bus' then 1 else 0 end) as bus,
       sum(case when vehicle_type = 'car' then 1 else 0 end) as car
from (select min(Timestamp) as startts from table t) m cross join
     (select 1 as n union all select 2 union all select 3) n left join
     table t
     on t.timestamp >= m.startts + n.n - 1 and 
        t.timestamp < m.startts + n.n
group by m.startts + n.n;

This is a little dangerous because of the floating point arithmetic, but it will probably work for your purposes.

Upvotes: 3

Related Questions