Bug #29381 Invalid result of SEC_TO_TIME
Submitted: 27 Jun 2007 9:11 Modified: 1 Aug 2007 17:20
Reporter: Lars Johansson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.19, 5.0, 4.1 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: 838:59:59, SEC_TO_TIME

[27 Jun 2007 9:11] Lars Johansson
Description:
The result from function SEC_TO_TIME has a max return value 838:59:59.
I seen this reported before (e.g. Bug #20203), but the reproduction of the bug is unnecessary complex.
Feeding the function with a to-high-value results in returning 838:59:59.  

How to repeat:
SELECT SEC_TO_TIME(3600000),3600000/3600;
[27 Jun 2007 9:36] Sveta Smirnova
Thank you for the report.

Reclassified as documentation bug as documentation is not clear about this limitation is expected. See also Martin Friebe's comments to bug #20203
[27 Jun 2007 14:27] Lars Johansson
Hi, thanks for your prompt reply.
If I understand you correctly the max value 838:59:59 is intended.

As we have a need for higher values (some thousend hours) a collegue delveloped a function showing hours and minutes as a temporary circumvention.
(We do not like this limit and would be happy if the restriction is removed.)
Cut, paste & enjoy: 

CREATE FUNCTION HOUR_MINUTES (SECS BIGINT) 
  RETURNS CHAR(50)
   DETERMINISTIC
    BEGIN
     DECLARE HOUR CHAR(40);
     DECLARE MINUTES CHAR(10);

     SET HOUR = ROUND(CASE WHEN MOD(SECS,3600) > 3570 THEN TRUNCATE((SECS/ 3600), 0) + 1 
                ELSE TRUNCATE((SECS/ 3600), 0)
                END);

     SET MINUTES = ROUND(CASE WHEN MOD(SECS,3600) >3570 THEN 0
                   ELSE MOD(SECS,3600)/60
                   END);                 

     IF MINUTES < 10 THEN SET MINUTES = CONCAT( '0', MINUTES); END IF;

     RETURN CONCAT( HOUR, ':', MINUTES);
    END
[1 Aug 2007 17:20] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated manual to indicate that SEC_TO_TIME() returns a TIME, is constrained to the range of the TIME data type, and a warning occurs for arguments resulting in values outside of that range.