SQL query to detect when accumulated value reaches limit

user_1035897 0
Viewed 46

Given the following table in PostgreSQL

CREATE TABLE my_table (
    time TIMESTAMP NOT NULL,
    minutes INTEGER NOT NULL);

I am forming a query to detect when the accumulated value of 'minutes' crosses an hour boundary. For example with the following data in the table:

time             | minutes
-------------------------
<some timestamp> | 55
<some timestamp> | 4

I want to know how many minutes remain before we reach 60 (one hour). In the example the answer would be 1 since 55 + 4 + 1 = 60.

Further, I would like to know this at insert time, so if my last insert made the accumulated number of minutes cross an "hour boundary" I would like it to return boolean true.

My naive attempt, without the insert part, looks like this:

    SELECT
        make_timestamptz(
            date_part('year', (SELECT current_timestamp))::int,
            date_part('month', (SELECT current_timestamp))::int,
            date_part('day', (SELECT current_timestamp))::int,
            date_part('hour', (SELECT current_timestamp))::int,
            0,
            0
        ) AS current_hour,

        SUM(minutes) as sum_minutes
    FROM
        my_table
    WHERE
        sum_minutes >= 60

I would then take a row count above 0 to mean we crossed the boundary. But it is hopelessly inelegant, and does not work. Is this even possible? Would be possible to make it somewhat performant?

I am using Timescaledb/PostgreSQL on linux.

accumulate timescaledb timestamp postgresql sql
2 Answers

Hmmm . . . insert doesn't really return values. But you can use a CTE to do the insert and then sum the values after the insert:

with i as (
      insert into my_table ( . . . )
          values ( . . . )
          returning *
     )
select ( coalesce(i.minutes, 0) + coalesce(t.minutes, 0) ) > 60
from (select sum(minutes) as minutes from i) i cross join
     (select sum(minutes) as minutes from my_table) t
user_11440350

The INSERT could look like this:

WITH cur_sum AS (
   SELECT coalesce(sum(minutes), 0) AS minutes
   FROM my_table
   WHERE date_trunc('hour', current_timestamp) = date_trunc('hour', time)
)
INSERT INTO my_table (time, minutes)
SELECT current_timestamp, 12 FROM cur_sum
RETURNING cur_sum.minutes + 12 > 60;

This example inserts 12 minutes at the current time.

user_64643080
Related