On Mon, 24 Oct 2005 [EMAIL PROTECTED] wrote: > "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
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 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
