How to aggregate rows in the range of timestamp in vertica db (vsql)

Question

Suppose I have a table with data like this:

         ts          | bandwidth_bytes
---------------------+-----------------
 2021-08-27 22:00:00 |    3792
 2021-08-27 21:45:00 |    1164
 2021-08-27 21:30:00 |    7062
 2021-08-27 21:15:00 |    3637
 2021-08-27 21:00:00 |    2472
 2021-08-27 20:45:00 |    1328
 2021-08-27 20:30:00 |    1932
 2021-08-27 20:15:00 |    1434
 2021-08-27 20:00:00 |    1530
 2021-08-27 19:45:00 |    1457
 2021-08-27 19:30:00 |    1948
 2021-08-27 19:15:00 |    1160

I need to output something like this:

         ts          | bandwidth_bytes
---------------------+-----------------
 2021-08-27 22:00:00 |    15,655
 2021-08-27 21:00:00 |    7166
 2021-08-27 20:00:00 |    6095

I want to do sum bandwidth_bytes over 1 hour timestamp of data. I want to do this in vsql specifically.

More columns are present but for simplification I have shown only these two.

Have you tried rounding your timestamp to the hour? – Andrew Aug 27 '21 at 18:02 — Andrew, Aug 27 '21 at 18:02

score 1 · Answer 1 · answered Aug 27 '21 at 18:16

1

You can use date_trunc():

select [date_trunc('hour', ts)][1] as ts_hh, sum(bandwidth_bytes)
from t
group by ts_hh;

answered Aug 27 '21 at 18:16

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Aug 28 '21 at 14:23

Use Vertica's lovely function TIME_SLICE().

You can't only go by hour, you can also go by slices of 2 or 3 hours, which DATE_TRUNC() does not offer.

You seem to want all between 20:00:01 and 21:00:00 to belong to a time slice of 21:00:00. In both DATE_TRUNC() and TIME_SLICE(), however, it's 20:00:00 to 20:59:59 that belongs to the same time slice. So I subtracted one second before applying TIME_SLICE() .

WITH
-- your in data ...
indata(ts,bandwidth_bytes) AS (
          SELECT TIMESTAMP '2021-08-27 22:00:00',3792
UNION ALL SELECT TIMESTAMP '2021-08-27 21:45:00',1164
UNION ALL SELECT TIMESTAMP '2021-08-27 21:30:00',7062
UNION ALL SELECT TIMESTAMP '2021-08-27 21:15:00',3637
UNION ALL SELECT TIMESTAMP '2021-08-27 21:00:00',2472
UNION ALL SELECT TIMESTAMP '2021-08-27 20:45:00',1328
UNION ALL SELECT TIMESTAMP '2021-08-27 20:30:00',1932
UNION ALL SELECT TIMESTAMP '2021-08-27 20:15:00',1434
UNION ALL SELECT TIMESTAMP '2021-08-27 20:00:00',1530
UNION ALL SELECT TIMESTAMP '2021-08-27 19:45:00',1457
UNION ALL SELECT TIMESTAMP '2021-08-27 19:30:00',1948
UNION ALL SELECT TIMESTAMP '2021-08-27 19:15:00',1160
)
SELECT
  TIME_SLICE(ts - INTERVAL '1 SECOND' ,1,'HOUR','END') AS ts
, SUM(bandwidth_bytes)    AS bandwidth_bytes
FROM indata
GROUP BY 1
ORDER BY 1 DESC;
         ts          | bandwidth_bytes 
---------------------+-----------------
 2021-08-27 22:00:00 |           15655
 2021-08-27 21:00:00 |            7166
 2021-08-27 20:00:00 |            6095

How to aggregate rows in the range of timestamp in vertica db (vsql)

2 Answers2