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.