Converting number of seconds to Minutes and Seconds

Database
Teradata Employee

Converting number of seconds to Minutes and Seconds

Hello!

I have a query from a user where he has a total number of seconds and a total number of calls. He wants to format the output so that instead of 100 seconds per call he would get 1 Minute 40 Seconds per Call.

When it comes to dates/times, and MOD I am clueless, so any help would be appreciated. I can see how it might work in my mind, but simply cannot translate it to SQL.

Many thanks!

Andrew
3 REPLIES
Teradata Employee

Re: Converting number of seconds to Minutes and Seconds

I believe I have cracked this one myself.

If someone could verify that this code is correct, I'd appreciate it:

,(Sum(Call_Length)/Count(Call_ID))/60||' Minutes and '|| MOD(Sum(Call_Length)/Count(Call_ID),60) || ' Seconds per call' as Average_Call_Length

Thanks!!!

Andrew

Re: Converting number of seconds to Minutes and Seconds

Hi Andrew,

I could try the following to see if it helps:

select cast((interval '100' second) as interval minute to second(0));

I hope this helps!

Cheers
N/A

Re: Converting number of seconds to Minutes and Seconds

Hi Fabio,
this will probably result in a "7453: Interval field overflow".

If the average phone call is not more than 9999 (a bit less than 7 days):
(SUM(Call_Length)/COUNT(Call_ID)) * INTERVAL '0000:01' MINUTE TO SECOND

This will work for up to ~ 27 years:
(SUM(Call_Length)/COUNT(Call_ID)) * INTERVAL '0000 00:00:01' DAY TO SECOND

Dieter