List:General Discussion« Previous MessageNext Message »
From:<jabbott Date:October 24 2005 4:05pm
Subject:Re: sum of time?
View as plain text  
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
-- 

Thread
sum of time?jabbott24 Oct
  • Re: sum of time?Dobromir Velev24 Oct
    • mysql + LVSWinn Johnston24 Jul
      • RE: mysql + LVSJimmy Guerrero24 Jul
        • RE: mysql + LVSWinn Johnston25 Jul
          • RE: mysql + LVS highjacked (mysql + NFS ramfs)Winn Johnston25 Jul
            • RE: mysql + LVS highjacked (mysql + NFS ramfs)Barry Newton25 Jul
  • Re: sum of time?SGreen24 Oct
    • Re: sum of time?Martijn Tonies24 Oct
      • Re: sum of time?Michael Stassen24 Oct
        • Re: sum of time?Michael Stassen24 Oct
    • Re: sum of time?Martijn Tonies24 Oct
    • Re: sum of time?Martijn Tonies24 Oct
      • Re: sum of time?SGreen24 Oct
        • Re: sum of time?jabbott24 Oct