MySQL - -838 hours?
I'm executing this script:
SELECT EXTRACT(HOUR FROM TIMEDIFF('2009-12-12 13:13:13', NOW()));
And i'm getting: -838. Is this the farthest MySQL can go up to when extracting?
You can overcome this limit with a workaround
select
datediff(now(),'2009-12-12 13:13:13') * 24
+ extract(hour from now())
- extract(hour from '2009-12-12 13:13:13') as hour_diff
This is indeed a limitation of the TIME
type.
By default, values that lie outside the TIME range but are otherwise legal are clipped to the closest endpoint of the range. For example, '-850:00:00' and '850:00:00' are converted to '-838:59:59' and '838:59:59'. Illegal TIME values are converted to '00:00:00'. Note that because '00:00:00' is itself a legal TIME value, there is no way to tell, from a value of '00:00:00' stored in a table, whether the original value was specified as '00:00:00' or whether it was illegal.
精彩评论