postgres query to return some of column value

Question

I've the following query which return the run time for 1200 videos , i want to get the total of run time in hh:mm:ss:ms

select  runtime from video where ready_date between '2017-07-01' AND '2017-07-30';

my output will look like

"00:00:33:07"

"00:00:37:09"

"00:01:52:02"

"00:00:41:05"

i've tried sum(runtime) but looks i'm doing the wrong thing here ,

any tips ?

Please include the table definition. Alternatively set up a test in sqlfiddle.com — Gary - Stand with Ukraine
– Gary - Stand with Ukraine, Commented Aug 15, 2017 at 7:29

Vao Tsun · Accepted Answer · 2017-08-15 09:40:23Z

1

one way - cast it to timestamp then to time then to interval, then it will sum up ok, eg:

vao=# with video(runtime) as (values('00:00:33:07'),('00:00:37:09'),('00:01:52:02'))
select sum(to_timestamp(runtime,'HH24:MI:ss:ms')::time::interval) from video;
     sum
-------------
 00:03:02.18
(1 row)

update: In your case would be smth like:

select sum(to_timestamp(runtime::text,'HH24:MI:ss:ms')::time::interval) 
from video 
where ready_date between '2017-07-01' AND '2017-07-30';

edited Aug 15, 2017 at 9:40

answered Aug 15, 2017 at 8:06

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jecki Over a year ago

i'll have to put 1200 values manully , can it join by select statement like this select runtime from video where ready_date between '2017-07-01' AND '2017-07-30';

Jecki Over a year ago

Perfect , all i need :) Thank Vao

Collectives™ on Stack Overflow

postgres query to return some of column value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related