2 messages in com.mysql.lists.mysqlRe: timediff and subtime: when is the...
FromSent OnAttachments
schlubediwup21 Feb 2005 23:46 
Mike Rains22 Feb 2005 01:31 
Subject:Re: timediff and subtime: when is the result negativ?
From:Mike Rains (sirh@gmail.com)
Date:02/22/2005 01:31:30 AM
List:com.mysql.lists.mysql

This is not surprising behaviour. If you subtract 12 from 10, you get -2 every time.

If you want your result to be positive, make it the first parameter and subtract the smaller (earlier) time from it. If your result is negative, then you know you have crossed into another day (13:00:00 - 17:00:00 = -04:00:00, meaning the 17:00:00 was from yesterday afternoon). If your times can be more than 24 hours apart, then working with SUBTIME won't help you much, by itself.

Reference: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

SUBTIME(expr,expr2)

SUBTIME() subtracts expr2 from expr and returns the result. expr is a time or datetime expression, and expr2 is a time expression.