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

user_12118888 0
Viewed 28

From MySQL dev site -

MAKETIME function -

  • 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?

temporal time datetime function mysql
2 Answers

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))
ysth_173890

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
user_135080
Related