2

I am having to Cast() a Text datatype to a TIME datatype. THe issue I'm running into is when the results display i'm loosing the hh:mm:ss format. The syntax I am using is Select SUM(CAST(salestime As Time)) From testData; How do I display my query results in hh:mm:ss format? I also tried to use this syntax SUM(Cast(Time_Format(salesTime, "%h, %i, %s") As Time)) but that is returning a whole number

EDIT
A few sample of the times in the TEXT column are:

0:05:58
0:00:00
0:31:54
0:23:26
1

You can use this

Schema (MySQL v8.0)

CREATE TABLE Table1
    (`salesTime` varchar(7))
;
    
INSERT INTO Table1
    (`salesTime`)
VALUES
    ('0:05:58'),
    ('0:00:00'),
    ('0:31:54'),
    ('0:23:26')
;

Query #1

SELECT SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(`salesTime`, "%H:%i:%s"))))  FROM Table1;

| SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(`salesTime`, "%H:%i:%s")))) |
| --------------------------------------------------------------------- |
| 01:01:18                                                              |

View on DB Fiddle

But TIME can only have the maximum of 838:59:59, if your SUM get bigger, you need to convert the seconds also into dd.hh:mm:ss

| improve this answer | |
  • This returns 00:00:00 for all of my SUMS() – jamesMandatory Jul 26 at 16:04
  • please add you data to show what data salestime, i assumed that your query works. – nbk Jul 26 at 16:05
  • edited to add a few times – jamesMandatory Jul 26 at 16:07
  • Your cast didn't work, you i used time_Format, see example, – nbk Jul 26 at 16:20
1

you can convert hours and minutes to seconds, then sum the three totals and use the convert function to get a value in string formats or the cast function to get value in time format

select 
    convert(varchar, dateadd(second, z.total_hours+z.total_minutes+z.total_seconds, 0), 114) as myresult_asstring
    ,cast(dateadd(second, z.total_hours+z.total_minutes+z.total_seconds, 0) as time) as myresult_astime
from
    (
    select 
         sum(datepart(hour, m.mytime))*3600 as total_hours
        ,sum(datepart(minute, m.mytime))*60 as total_minutes
        ,sum(datepart(second, m.mytime)) as total_seconds
    from (
            select cast('0:05:58' as time) as mytime
            union
            select cast('0:00:00' as time) as mytime
            union
            select cast('0:31:54' as time) as mytime
            union
            select cast('0:23:26' as time) as mytime
        ) as m
    ) as z
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.