Let’s have a look at the TIMEDIFF() function in MySQL:
TIMEDIFF(expr1,expr2) - TIMEDIFF() returns expr1 – expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.
It seems that when TimeDiff is performed on 2 dates that exceed 36 days then a warning is thrown, and the resulting value is always capped to 838.
For example:
select time_format(timediff(‘2009-02-05 00:00:00′,’2009-01-01 00:00:00′),’%H’) as TimeDifference
If you execute the above query MySQL will return 838 instead of returning 840 (35 X 24 = 840).
The reason is precisely because TIME type in MySQL has an upper bound of 838:59:59. TIME values may range from ‘-838:59:59′ to ‘838:59:59′.
By using TIMESTAMPDIFF() function you can get around with this problem.
Let’s have a look at the TIMESTAMPDIFF() function in MySQL:
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) – Returns datetime_expr2 - datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
It is bit confusing timediff and timestampdiff expects the times in opposite order.
Now let’s try timestampdiff() with same dates
select timestampdiff(hour,’2009-01-01 00:00:00′,’2009-02-05 00:00:00′) as TimeDifference
If you execute the above query MySQL will return 840. Even if the difference is more than 840 it will not throw any exceptions or any warning. Hence we can use TIMESTAMPDIFF() function where the difference is more than 838 and can get the output in any of FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
There you have it … I hope you enjoyed this article. Please leave comments / suggestions / questions if you have.
Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.
Comments
No comments yet.
Leave a comment