Question 222512 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql problems

i have a table like this:

name   used   time  
asd    10     15:00  
bsf    15     15:00  
asd    20     14:55  
bsf    0      14:55

i need to make a query that returns values like this:
the result i need for the grafana timeseries is:

total   tm
25       15:00
20       14:55

i’ve tried using:

SELECT
 DISTINCT(time) as tm,
 sum(used) as total
FROM table
GROUP BY tm

but that doesnt works everything i try gives me the repeated time values

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I managed to solve the problem by using the date_trunc() function to select the time value since the column was of type timestamptz the query ended up looking like this select date_trunc('minute', time) as tm, sum(used) as total from table group by tm


  2. 0

    Reviewing the postgres docs is a great place to start when working with timestamps. Here’s a way to group by HH:MI:SS and aggregate:

    with my_table as (
  select current_timestamp as time_column, 20 as used union all
  select current_timestamp, 5 union all
  select current_timestamp - INTERVAL '10 Seconds', 15
  )
select to_char(time_column,'HH24:MI:SS') as time_col, sum(used) as used
from my_table
group by 1
order by 1;
    time_col used
    19:43:35 15
    19:43:45 25

    Basically, this type of casting is your friend:

    to_char(time_column,'HH24:MI:SS')
    Login or Signup to reply.
  3. 0

    You are looking for a simple GROUP BY

    CREATE TABLE Table1
    ("name" varchar(3), "used" int, "time" varchar(5))
;
    
INSERT INTO Table1
    ("name", "used", "time")
VALUES
    ('asd', 10, '15:00'),
    ('bsf', 15, '15:00'),
    ('asd', 20, '14:55'),
    ('bsf', 0, '14:55')
;


    SELECT
SUM("used"), "time"
FROM Table1
GROUP BY "time"
    sum time
    25 15:00
    20 14:55 
    SELECT 2

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search