List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:October 24 2005 2:16pm
Subject:Re: sum of time?
View as plain text  

> 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

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