liczenie timstamp Interwał

0

Baza zawiera timestamp i user_ID:
1584126598246 19553
1584126595542 10892
1584126582310 15190
1584126352886 4125
1584126326657 21052
1584126284591 4125
1584126132608 20899
1584126117460 6521
1584126063430 4125
1584126045989 15190
1584126045989 15190
1584126034702 15190
1584126022868 1902
1584126002615 18526
1584125799495 4125
1584125712397 15190
1584125707143 15190
1584125667143 7183
1584125603914 20899
1584125563879 15149
1584125551711 4125
1584125430211 20223
1584125357285 18602
1584125308479 21719
1584125304936 15190

Jak w sqlite zaokrąglić czasy np do 15min i policzyć ile timastampów jest w tych 15 minutach?

Wynik ma wyglądać tak:

2020-03-12 2200 3
2020-03-12 2200 2
2020-03-12 2200 5
itd.

1

zrobione


select 
  strftime('%Y-%m-%d %H:', datetime(timestamp/1000,'unixepoch')) ||
  case when ((cast(strftime('%M', datetime(timestamp/1000,'unixepoch')) as int) / 15) * 15) = 0 then '0' else '' end ||
  ((cast(strftime('%M', datetime(timestamp/1000,'unixepoch')) as int) / 15) * 15) || ':00' period,
  count(*) counter
from detections
WHERE  (datetime(timestamp/1000,'unixepoch')> "2020-03-12 21:00:00" 
AND 
datetime(timestamp/1000,'unixepoch')< "2020-03-13 07:00:00" ) 

group by period

1 użytkowników online, w tym zalogowanych: 0, gości: 1