All Forums Database
14 Jan 2008
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

Andrew Livingston EMEA - Customer Education Consultant Teradata UK 206 Marylebone Road London NW1 6LY United Kingdom M: +44 7785 971 080 E-mail: Andrew.Livingston@Teradata.com
14 Jan 2008

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_LengthThanks!!!Andrew

Andrew Livingston
EMEA - Customer Education Consultant
Teradata UK
206 Marylebone Road
London NW1 6LY
United Kingdom

M: +44 7785 971 080
E-mail: Andrew.Livingston@Teradata.com

famalau 43 posts Joined 08/07
15 Jan 2008

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

Regards,

Fabio

dnoeth 4628 posts Joined 11/04
15 Jan 2008

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 SECONDThis will work for up to ~ 27 years:(SUM(Call_Length)/COUNT(Call_ID)) * INTERVAL '0000 00:00:01' DAY TO SECONDDieter

Dieter

You must sign in to leave a comment.