> 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
