On Mon, 24 Oct 2005 SGreen@stripped wrote:
> "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
Hey I have another question. If I was running MySQL 5, would this be a great thing to
create as a view? That way I could just send the userID as a select for the hours and
get them back? Or would this be a waste as it is easy to get with a query anyway? If
so, when should I use a view?
--ja
--
