SQLITE - Round timestam and count
lazarus / 16-Databases

----------------------------------------------------------------------
mpknap · 2020-03-17 17:46

Sqlite database for the TIMESTAMP column (unix time, changed to dates
below)

2020-03-12 21:18:44
2020-03-12 21:27:34
2020-03-12 21:37:25
2020-03-12 21:38:34
2020-03-12 21:39:44
2020-03-12 21:41:24
2020-03-12 21:43:41
2020-03-12 21:45:51
2020-03-12 21:49:47
2020-03-12 21:52:05
2020-03-12 22:00:30
2020-03-12 22:12:48
2020-03-12 22:17:37
2020-03-12 22:18:30
2020-03-12 22:20:01
2020-03-12 22:20:44

How to round and count to get something like this?


2020-03-12 21:15:00   |   2
2020-03-12 21:30:00   |   5
2020-03-12 21:45:00   |   3

----------------------------------------------------------------------
mpknap · 2020-03-17 20:34

Solved

Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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(*) counterfrom detectionsWHERE
 (datetime(timestamp/1000,'unixepoch')> "2020-03-12 21:00:00" AND
datetime(timestamp/1000,'unixepoch')< "2020-03-13 07:00:00" )  group
by period

----------------------------------------------------------------------
Zvoni · 2020-03-18 07:17

Why not use BETWEEN?

WHERE  datetime(timestamp/1000,'unixepoch') BETWEEN "2020-03-12
21:00:01" AND "2020-03-13 06:59:59"

----------------------------------------------------------------------
mpknap · 2020-03-18 21:09

by being amateur?  thx

----------------------------------------------------------------------
mpknap · 2020-04-24 05:50

ok, now step two.
I added a second OULU1 table.
Its structure is :


Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Timestamp,FractionalDate,UncorrectedCountRate,CorrectedCount
Rate,Pressure2020-01-01 00:00:00 ,1.0000000,6796,6780,999.432020-01-01
00:15:00 ,1.0104167,6774,6763,999.522020-01-01 00:30:00
,1.0208333,6793,6778,999.452020-01-01 00:45:00
,1.0312500,6809,6790,999.372020-01-01 01:00:00
,1.0416667,6862,6838,999.282020-01-01 01:15:00
,1.0520833,6834,6808,999.24....... 
The code from the previous posture counts the number of detections
every 15 minutes. The OULU1 table also has records every 15 minutes.

I want to connect the tables to make a chart in DBBrowserSqlite for :

a.PERIOD a.Counter b.UncorrectedCountRate

I'm sure the code has an error because it's several hours long
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select    
strftime('%Y-%m-%d %H:',datetime(a.timestamp/1000,'unixepoch')) || 
case when ((cast(strftime('%M',
datetime(a.timestamp/1000,'unixepoch')) as int) / 15) * 15) = 0 then
'0' else '' end ||  ((cast(strftime('%M',
datetime(a.timestamp/1000,'unixepoch')) as int) / 15) * 15) || ':00'
period,  count(*) counter , b.UncorrectedCountRatefrom detections  as
a left JOIN oulu1 b on period=datetime(b.timestamp)WHERE
 (datetime(a.timestamp/1000,'unixepoch')> "2020-03-12 21:00:00" AND
datetime(a.timestamp/1000,'unixepoch')< "2020-03-13 07:00:00" )
  group by period ORDER by datetime(timestamp/1000,'unixepoch')