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