"Martijn Tonies" <[EMAIL PROTECTED]> wrote on 10/24/2005 10:16:21 AM:
> > > > Michael Stassen wrote: > > <snip> > > > Second, no, it won't overflow: > > > > > > mysql> SELECT SEC_TO_TIME(60*60*24*5); > > > +-------------------------+ > > > | SEC_TO_TIME(60*60*24*5) | > > > +-------------------------+ > > > | 120:00:00 | > > > +-------------------------+ > > > 1 row in set (0.00 sec) > > > > > > mysql> SELECT SEC_TO_TIME(60*60*24*50); > > > +--------------------------+ > > > | SEC_TO_TIME(60*60*24*50) | > > > +--------------------------+ > > > | 1200:00:00 | > > > +--------------------------+ > > > 1 row in set (0.00 sec) > > > > > > SEC_TO_TIME() is not limited to 24 hours. > > > > > > > I should have added that the limits of a TIME column are documented in the > > manual <http://dev.mysql.com/doc/refman/4.1/en/time.html>: > > > > TIME values may range from '-838:59:59' to '838:59:59'. The reason for > > which the hours part may be so large is that the TIME type may be used > > not only to represent a time of day (which must be less than 24 hours), > > but elapsed time or a time interval between two events as well. (Note > > that this interval may be much greater than 24 hours, or even > negative.) > > That's actually a very weird definition for a TIME datatype :-) > > It should have an "interval" datatype for such operations. > > > So some care may be needed if you will be storing the result, because > > SEC_TO_TIME() can return a time outside of a TIME column's allowable > range. > > With regards, > > Martijn Tonies > Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL > Server > Upscene Productions > http://www.upscene.com > Database development questions? Check the forum! > http://www.databasedevelopmentforum.com > The "interval" datatype? I don't see "interval" as an option for MySQL. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html. Yes, "interval" fields are defined as part of SQL2003 but MySQL doesn't have them (yet) which is probably why the TIME datatype has such a wide range. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
