Postgresql problems

Question

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

Your query should error out. You have no FROM clause. Also, why not just `select time, sum(used) from table group by time` — Isolated, Aug 03 '23 at 19:29
i didnt think it was necesary to put here the from part sorry, because that returns the repeated time values — Mauricio Del Aguila, Aug 03 '23 at 19:31
If you're getting repeated time values, that must mean your times look different than what you are showing us. Perhaps you have HH:MM:SS.... rather than just what are you displaying of HH:MM. What is the column type? — Isolated, Aug 03 '23 at 19:32
Then you'll want to select and group by the part of the timestamp which you care about. Postgres docs have good documentation on working with timestamps. Also, there are numerous Q&A on SO about this. — Isolated, Aug 03 '23 at 19:34
how can i slect and group by the part of the timestamp? what i need is the HH:MM:SS sorry but im just starting using postgresql and sorry by my bad writing english isn't my main language — Mauricio Del Aguila, Aug 03 '23 at 19:39
Give a proper sample. You actual data is a timestamp, but you only give a time in the sample data. — Eric, Aug 03 '23 at 19:45

score 0 · Answer 1 · answered Aug 03 '23 at 19:45

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')

score 0 · Accepted Answer · edited Aug 04 '23 at 07:43

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

edited Aug 04 '23 at 07:43

kometen

6,536
6
41
51

answered Aug 03 '23 at 19:48

Mauricio Del Aguila

13
4

score 0 · Answer 3 · answered Aug 03 '23 at 19:49

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

answered Aug 03 '23 at 19:49

nbk

45,398
8
30
47

Are you 100% sure about the VARCHAR to store a TIME? In that case, 'three o'clock in the afternoon' would be a valid time. Right? – Frank Heikens Aug 03 '23 at 19:52
i think he uses a date_trunc, but as the didn't post the original data, we can assume any value, as it is the column that will be grouped by, so the data tyoe doesn't matter for the concept – nbk Aug 03 '23 at 20:08

Postgresql problems

3 Answers3