Search code examples
riakriak-ts

Count by minute in Riak TS

I'm trying to grasp the recently added group by in Riak TS.

I'm unable to find a way to group my results by minute, e.g. count. I'll show an example below.

CREATE TABLE Results
(
   result      VARCHAR   NOT NULL,
   time        TIMESTAMP NOT NULL,
   PRIMARY KEY (
      (QUANTUM(time, 1, 'm')),
      time
   )
)

Inserts

INSERT INTO FreightMinuteResult VALUES ('Novo', '2017-12-07 12:03:45Z');
INSERT INTO FreightMinuteResult VALUES ('Novo', '2017-12-07 12:04:45Z');
INSERT INTO FreightMinuteResult VALUES ('Novo', '2017-12-07 12:05:45Z'); 
INSERT INTO FreightMinuteResult VALUES ('Novo', '2017-12-07 12:05:46Z');

Query

select count(*) from FreightMinuteResult where time > '2017-12-07 12:01:00Z' and time < '2017-12-07 12:06:00Z' group by time;

The result is

+--------+--------------------+ |COUNT(*)| time | +--------+--------------------+ | 1 |2017-12-07T12:04:45Z| | 1 |2017-12-07T12:03:45Z| | 1 |2017-12-07T12:05:45Z| | 1 |2017-12-07T12:05:46Z| +--------+--------------------+

How to count the number of occurrences per minute using Riak TS?

Thanks.

Solution

  • The quantum is used to organize the data in the backend to streamline query operations, while group by uses the exact value of the specified field. The timestamps 2017-12-07T12:05:45Z and 2017-12-07T12:05:46Z occur in the same minute and will therefore be stored in the same location on disk, but they are still stored as distinct second-resolution timestamp values that will be grouped separately.

    If you want to be able to group by the minute you will need to either round the timestamps when inserting, or modify your table to include a minute field that can be grouped.