2

I am trying to sum time values and have it in the format of hours:minutes:seconds i.e. 100:30:10.

SEC_TO_TIME(SUM(TIME_TO_SEC(ActualHours))) AS Hours

But I'm having a problem because time's max value is 838:59:59. So if summing the time is over this value it won't show i.e. if it equals 900 hours it will show as 838:59:59 which is wrong.

How do I the display the total hours if it is over 838:59:59?

user123456789
  • 1,914
  • 7
  • 44
  • 100
  • I think you need not convert it back using `SEC_TO_TIME` and let it be in seconds. In your application code you can do the conversion. – Madhur Bhaiya Oct 09 '18 at 14:14
  • use a series of `MOD` and `DIV` operations, to extract seconds (remainder dividing by 60), minutes (subtract seconds, divide by 60, then remainder of another divide by 60, to get minutes, ... – spencer7593 Oct 09 '18 at 14:17

3 Answers3

2

If I had to do this conversion in SQL, I would do something like this:

SELECT CONCAT(              (   _secs_    DIV 3600)
         , ':'
         , RIGHT(CONCAT('0',(   _secs_    DIV 60  ) MOD 60 ),2)
         , ':'
         , RIGHT(CONCAT('0',(   _secs_              MOD 60)),2)
       ) AS `h:mm:ss`

We can just replace the _secs_ with the expression that returns the number of seconds we want to convert. Using the expression given in the question, we get something like this:

SELECT CONCAT(              (  SUM(TIME_TO_SEC(ActualHours))  DIV 3600)
         , ':'
         , RIGHT(CONCAT('0',(  SUM(TIME_TO_SEC(ActualHours))  DIV 60  ) MOD 60 ),2)
         , ':'
         , RIGHT(CONCAT('0',(  SUM(TIME_TO_SEC(ActualHours))            MOD 60)),2)
       ) AS `h:mm:ss`

DEMONSTRATION

The syntax provided in this answer is valid in MySQL 5.6. As a demonstration, using a user-defined variable @_secs as the expression number of seconds:

Set user-defined variable for demonstration:

SELECT @_secs_ := ( 987 * 3600 ) + ( 5 * 60 ) + 7  ;

returns

@_secs := ( 987 * 3600 ) + ( 5 * 60 ) + 7  
-----------------------------------------
                                  3553507

demonstrating the query pattern:

SELECT CONCAT(              (  @_secs_    DIV 3600) 
         , ':'
         , RIGHT(CONCAT('0',(  @_secs_    DIV 60  ) MOD 60 ),2)
         , ':'
         , RIGHT(CONCAT('0',(  @_secs_              MOD 60)),2)
       ) AS `hhh:mm:ss`

returns

  hhh:mm:ss
  ---------
  987:05:07
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • I get the an error with this query: error Error Code: 1064. You have an error in your SQL syntax; – user123456789 Oct 09 '18 at 15:09
  • I believe the syntax provided in this question is valid. The expression after the SELECT keyword should be valid a replacement for the expression shown in the question, obviously with the placeholder `_secs_` replaced with an expression that is valid in the context of the query. – spencer7593 Oct 09 '18 at 16:16
  • Instead of 'RIGHT(CONCAT())' it might be cleaner to use LPAD as in this answer: https://stackoverflow.com/a/23294152/2988142 Also CONCAT_WS(':', ...) looks a bit better in my opinion. – user2988142 Jan 15 '19 at 11:32
0

Use some simple math to concat a time period from seconds,replace 35000 with your column.

SELECT CONCAT(FLOOR(35000/3600),':',FLOOR((35000%3600)/60),':',(35000%3600)%60)

A fiddle to play with

Mihai
  • 26,325
  • 7
  • 66
  • 81
0

Here is one way we can do this:

SELECT
    CONCAT(CAST(FLOOR(seconds / 3600) AS CHAR(50)), ':',
           CAST(FLOOR(60*((seconds / 3600) - FLOOR(seconds / 3600))) AS CHAR(50)), ':',
           CAST(seconds % 60 AS CHAR(50))) AS time
FROM yourTable;

For an input of 10,000,000 (ten million) seconds, this would generate:

2777:46:40

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360