From: Martijn Tonies
Date: October 24 2005 2:16pm
Subject: Re: sum of time?
List-Archive: http://lists.mysql.com/mysql/190759
Message-Id: <03a101c5d8a5$82454b70$c802a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit



> 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