MySQL time field doesnt seem to take midnight into consideration when adding hours-CodePudding

I just ran into the weird behaviour of MySQL when adding intervals to my current time. The query I ran was as follows:

select curtime()   interval (select (-(1) * setup.offset) from setup) hour;

-- setup.offset is just an int field from a table. In this case its = 3

the result:

 --------------------------------------------------------------------------- 
| curtime()   interval (select (-(1) * `setup`.`offset`) from `setup`) hour |
 --------------------------------------------------------------------------- 
| 20:29:13                                                                  |
 ---------------------------------------------------------------------------

now, if i do the same with 1 instead of -1 I get:

 ------------------------------------------------------------------------ 
| curtime()   interval (select (1 * `setup`.`offset`) from `setup`) hour |
 ------------------------------------------------------------------------ 
| 26:29:20                                                               |
 ------------------------------------------------------------------------

which is obviously not correct. Can anybody elaborate and maybe give me an alternative

CodePudding user response：

Cast the result to a DATETIME to make it wrap to the next day, then extract the time from that.

> select TIME(cast(curtime()   interval 8 hour as datetime));
 ----------------------------------------------------- 
| TIME(cast(curtime()   interval 8 hour as datetime)) |
 ----------------------------------------------------- 
| 01:07:23                                            |
 -----------------------------------------------------

Or use NOW() instead of CURTIME() so it includes the date to begin with.

> select TIME(now()   interval 8 hour);
 ------------------------------- 
| TIME(now()   interval 8 hour) |
 ------------------------------- 
| 01:10:14                      |
 -------------------------------