SQLITE SUM of timestamp values - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

SQLITE SUM of timestamp values

sqlite add tag

DeFacto

Hello,

Is it possible, to separate the records into portions and return the sum of each portion in SQLITE?
Let's say i have a table

```
CREATE TABLE myTable (`Start` TIME, `END` TIME, `Cnt`);
INSERT INTO myTable (`Start`, `END`, `Cnt`) VALUES
  ('22:04:02', '22:04:12', 8),
  ('22:04:12', '22:04:22', 22),
  ('22:04:22', '22:04:32', 10),
  ('22:04:32', '22:04:42', 9),
  ('22:04:42', '22:04:52', 92),
  ('22:04:52', '22:05:02', 14),
  ('22:05:02', '22:05:12', 88),
  ('22:05:12', '22:05:22', 24),
  ('22:05:22', '22:05:32', 30);
```
What i am trying to achive is to get the `SUM`of `Cnt` values, by e.g. 20 seconds, between specific time interval.
```
SELECT SUM(Cnt) FROM myTable WHERE START >= '22:04:32' AND END <= '22:05:22' 
```
will return the sum of all records between spcified time, but the desired result would be:
| Time | Sum |
|:----|:----|
| 22:04:52|101|
| 22:05:12|102|
| 22:05:22|24|

[fiddle](https://dbfiddle.uk/aMVhiefO)

Top Answer

Luuk

Your fiddle was from 3.27, Since that some date/time function got improved

```
SELECT time(datetime(Start - Start % 20,'unixepoch')) as time,
       sum(Cnt) as sum
FROM (
  SELECT 
    unixepoch(DATE()||' '||Start) as Start,
    unixepoch(DATE()||' '||END) as END,
    Cnt
  FROM myTAble
)
GROUP BY time(datetime(Start - Start % 20,'unixepoch'))
```

|time	|sum|
|-|-|
|22:04:00	|30|
|22:04:20	|19|
|22:04:40	|106|
|22:05:00	|112|
|22:05:20	|30|


see: https://dbfiddle.uk/Fw3I0QZA

1 Answer