Subtract minutes from time value

Question

I have a time field in my database, it has a value of 22:05

I want to subtract 5 minutes. When the field is datetime, I use sub_date and it works fine, but I cannot get it using the time field. I get a warning that I don’t know about

mysql> SELECT scheduleFinalTime FROM clientSchedule WHERE clientScheduleID = 3; +-------------------+ | scheduleFinalTime | +-------------------+ | 22:05:00 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT date_sub(scheduleFinalTime, INTERVAL 5 MINUTE) FROM clientSchedule WHERE clientScheduleID = 3; +------------------------------------------------+ | date_sub(scheduleFinalTime, INTERVAL 5 MINUTE) | +------------------------------------------------+ | NULL | +------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------+ | Warning | 1264 | Out of range value for column 'scheduleFinalTime' at row 1 | +---------+------+------------------------------------------------------------+ 1 row in set (0.00 sec) 

Thank you in advance

Answer 1

Mysql has SUBTIME () for this.

 SUBTIME(expr1,expr2) SUBTIME() returns expr1 − expr2 expressed as a value in the same format as expr1. expr1 is a time or datetime expression, and expr2 is a time expression. mysql> SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002'); -> '2007-12-30 22:58:58.999997' mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998'); -> '-00:59:59.999999' 

Answer 2

For those using mysql 4.x

 SEC_TO_TIME(TIME_TO_SEC(t2) - TIME_TO_SEC(t1))