CODEWITHSUNDEEP
mysqltime
user1794656
user1794656

Reputation:

summing time column in mysql

I have a column in one of my tables, which is TIME format (00:00:00). I am trying to sum the entire column and display it as same (00:00:00).

I have tried using the following but it is not giving me anywhere near the correct answer.It's giving me 22.12:44:00 and manual calcaulation tells me it should be close to 212:something:something

SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( vluchttijd ) ) ) AS totaltime FROM tbl_vluchtgegevens

Any recommendations?

Upvotes: 3

Views: 6163

Answers (3)

Egene
Egene

Reputation: 301

This worked for me:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(vluchttijd))) AS totaltime FROM tbl_vluchtgegevens;

Upvotes: 0

Sylvain Leroux
Sylvain Leroux

Reputation: 52060

In MySQL, the TIME type is rather limited in range. Moreover many time function do not accept values greater that 23:59:59, making it really usable only to represent the time of the day.

Given your needs, your best bet is probably to write a custom function that will mimic SEC_TO_TIME but allowing much greater range:

CREATE FUNCTION SEC_TO_BIGTIME(sec INT)
RETURNS CHAR(10) DETERMINISTIC
BEGIN
    SET @h = sec DIV 3600;
    SET @m = sec DIV 60 MOD 60;
    SET @s = sec MOD 60;

    RETURN CONCAT(
          LPAD(@h, 4, '0'),
          ':',
          LPAD(@m, 2, '0'),
          ':',
          LPAD(@s, 2, '0')
      );

END;

And here is how to use it:

create table tbl (dt time);
insert tbl values
  ('09:00:00'), ('01:00:00'), ('07:50:15'), ('12:00:00'), 
  ('08:30:00'), ('00:45:00'), ('12:10:30');

select SEC_TO_BIGTIME(sum(time_to_sec(dt))) from tbl;

Producing:

+--------------------------------------+
| SEC_TO_BIGTIME(SUM(TIME_TO_SEC(DT))) |
+--------------------------------------+
| 0051:15:45                           |
+--------------------------------------+

See http://sqlfiddle.com/#!8/aaab8/1

Please note the result is a CHAR(10) in order to overcome TIMEtype limitations. Depending how you plan to use that result, that means that you may have to convert from that string to the appropriate type in your host language.

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172618

You can try like this:-

SELECT SEC_TO_TIME(SUM(SECOND(vluchttijd ))) AS totaltime FROM tbl_vluchtgegevens;

or try this(althoug this is not a good approach):

SELECT concat(floor(SUM( TIME_TO_SEC( `vluchttijd ` ))/3600),":",floor(SUM( TIME_TO_SEC( `vluchttijd ` ))/60)%60,":",SUM( TIME_TO_SEC( `vluchttijd ` ))%60) AS total_time 
FROM tbl_vluchtgegevens;

Edit:-

Try this:-

select  cast(sum(datediff(second,0,dt))/3600 as varchar(12)) + ':' + 
        right('0' + cast(sum(datediff(second,0,dt))/60%60 as varchar(2)),2) +
        ':' + right('0' + cast(sum(datediff(second,0,dt))%60 as varchar(2)),2)
from    TestTable

Working SQL Fidlle

Upvotes: 3

Related Questions