How to construct negative time less than 1 hour using MAKETIME function of MySQL?-CodePudding

From MySQL dev site -

MAKETIME(hour,minute,second)

Returns a time value calculated from the hour, minute, and second arguments.

The second argument can have a fractional part.
mysql> SELECT MAKETIME(12,15,30);
        -> '12:15:30'

Upon testing on my machine -

mysql> SELECT MAKETIME(1, 0, 0) AS output;
 ---------- 
| output   |
 ---------- 
| 01:00:00 |
 ----------

mysql> SELECT MAKETIME(1, 30, 0) AS output;
 ---------- 
| output   |
 ---------- 
| 01:30:00 |
 ----------

Now, trying to constructing negative time -

mysql> SELECT MAKETIME(-1, 0, 0) AS output;
 ----------- 
| output    |
 ----------- 
| -01:00:00 |
 -----------

mysql> SELECT MAKETIME(-1, 30, 0) AS output;
 ----------- 
| output    |
 ----------- 
| -01:30:00 |
 -----------

Now I try to construct the time -00:30:00 using MAKETIME function. I try -

mysql> SELECT MAKETIME(-0, 30, 0) AS output;
 ---------- 
| output   |
 ---------- 
| 00:30:00 |
 ----------

The result is not as intended. Then I try -

mysql> SELECT MAKETIME(0, -30, 0) AS output;
 -------- 
| output |
 -------- 
| NULL   |
 --------

I am getting a null output here.

I can't figure out a proper way to do it.

Can I possibly do it?

CodePudding user response：

This appears to be a gap in maketime's functionality; the mariadb documentation outright says "If minute or second are out of the range 0 to 60, NULL is returned." which seems to be the behavior of all mysql and mariadb versions I can test.

I suggest you use sec_to_time instead:

sec_to_time((0)*3600   (-30)*60   (0))

CodePudding user response：

This function doesn't seem usable for this purpose. You'll probably need some string manipulation and a final cast, as in:

SELECT CAST(CONCAT_WS(':', '-0', 30, 0) AS TIME);

-00:30:00