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 ?

https://stackoverflow.com/questions/952493/how-do-i-convert-an-interval-into-a-number-of-hours-with-postgres , You can check this. — Fahad Anjum, Jul 31 '17 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, Jul 31 '17 at 13:46

score 8 · Accepted Answer · answered Jul 31 '17 at 13:35

8

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 '17 at 13:35

Laurenz Albe

209,280
17
206
263

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_ – RKs Jul 31 '17 at 13:49
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 ...`. – Laurenz Albe Jul 31 '17 at 14:42
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 – RKs Jul 31 '17 at 15:06

Convert interval to number in postgresql

1 Answers1