"Martijn Tonies" <m.tonies@stripped> 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
