Convert interval to number in postgresql

Question

I have created a query which sums up the interval for respective date-time,

select sum(ts_polling) / count(ts_polling) as Average_Queue_Wait_Time ,  cast(time_start AS Date)
  from callcent_queuecalls group by cast(time_start AS date) order by time_start DESC;

Is there a way to convert Average_Queue_Wait_Time from interval data type to number ?

stackoverflow.com/questions/952493/… , You can check this. — Fahad Anjum
– Fahad Anjum, Commented Jul 31, 2017 at 13:22
I want to convert avergae_queue_wait_time to integer value. I tried using extract function as follows, SELECT EXTRACT(second FROM (select sum(ts_polling) / count(ts_polling) from callcent_queuecalls group by cast(time_start AS date) )) AS Queue_Wait_Time, cast(time_start AS DATE) AS DayOfMonth from callcent_queuecalls group by cast(time_start AS Date) order by cast(time_start AS DATE) DESC ---------- the following error was returned, ********** Error ********** ERROR: more than one row returned by a subquery used as an expression SQL state: 2100 — RKs
– RKs, Commented Jul 31, 2017 at 13:46

Laurenz Albe · Accepted Answer · 2017-07-31 13:35:17Z

9

You can get the number of seconds in an interval like this:

SELECT EXTRACT(epoch FROM INTERVAL '1 day 30 minutes 1.234 seconds');

┌───────────┐
│ date_part │
├───────────┤
│ 88201.234 │
└───────────┘
(1 row)

answered Jul 31, 2017 at 13:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

RKs Over a year ago

Is it possible to extract on a subquery ? For instance I ran the following statement and resulted in error

SELECT EXTRACT(second FROM (select sum(ts_polling) / count(ts_polling)   from callcent_queuecalls group by cast(time_start AS date) )) AS Queue_Wait_Time,  cast(time_start AS DATE) AS DayOfMonth from callcent_queuecalls group by cast(time_start AS Date) order by cast(time_start AS DATE) DESC

ERROR: more than one row returned by a subquery used as an expression SQL state: 2100

Laurenz Albe Over a year ago

You can only use such a subselect if it returns at most one row. You should use SELECT EXTRACT(second FROM sum(ts_polling) / count(ts_polling)) FROM ....

RKs Over a year ago

Excellent ! This gave me exactly the output I was looking for. I used this query in metabase dashboard and it looks awesome. Thank you for the help... Cheers

Collectives™ on Stack Overflow

Convert interval to number in postgresql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related