Problem with time_to_sec function

I am creating a pilot roster that includes total hours flown. The
query is:

SELECT a.pilot_id AS Pilot_id, a.pilot_num AS Pilot, a.name AS Name,
c.airport_name AS HUB, a.vatsim_id AS VATSIM, sec_to_time( sum(
time_to_sec( b.duration ) ) ) AS Duration
FROM pilots AS a, reports AS b, airports AS c
WHERE a.pilot_id = b.pilot_id
AND a.hub = c.airport_code
GROUP BY Pilot_id, Pilot, Name, HUB, VATSIM
ORDER BY Pilot
LIMIT 1000;

My question concerns this part of the select.

sec_to_time( sum( time_to_sec( b.duration ) ) ) AS Duration

This has been working fine since I started producing the report. The
problem that has now surfaced is that the calculation seems to max out
at 838 hours 59 Minutes and 59 Seconds. As I add a new row to the
table, the number of rows increases but the total hours remains at
838:59:59.

Is this a limitation of the time_to_sec/sec-to-time function? If so
can you suggest another way that I can total the hours flown.

Thanks,
Tom

"Call Me Tom" <Noone@nowhere.net> schreef in bericht
news:r0nro39ienag88ffaeq88d78klp5gkbl2h@4ax.com...
>I am creating a pilot roster that includes total hours flown. The
> query is:
>
> SELECT a.pilot_id AS Pilot_id, a.pilot_num AS Pilot, a.name AS Name,
> c.airport_name AS HUB, a.vatsim_id AS VATSIM, sec_to_time( sum(
> time_to_sec( b.duration ) ) ) AS Duration
> FROM pilots AS a, reports AS b, airports AS c
> WHERE a.pilot_id = b.pilot_id
> AND a.hub = c.airport_code
> GROUP BY Pilot_id, Pilot, Name, HUB, VATSIM
> ORDER BY Pilot
> LIMIT 1000;
>
> My question concerns this part of the select.
>
> sec_to_time( sum( time_to_sec( b.duration ) ) ) AS Duration
>
> This has been working fine since I started producing the report. The
> problem that has now surfaced is that the calculation seems to max out
> at 838 hours 59 Minutes and 59 Seconds. As I add a new row to the
> table, the number of rows increases but the total hours remains at
> 838:59:59.
>
> Is this a limitation of the time_to_sec/sec-to-time function? If so
> can you suggest another way that I can total the hours flown.
>
> Thanks,
> Tom

see: http://dev.mysql.com/doc/refman/5.0/en/time.html
9.3.2. The TIME Type
MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or
'HHH:MM:SS' format for large hours values). TIME values may range from
'-838:59:59' to '838:59:59'. The hours part may be so large because the TIME
type can be used not only to represent a time of day (which must be less
than 24 hours), but also elapsed time or a time interval between two events
(which may be much greater than 24 hours, or even negative).

"Luuk" <luuk@invalid.lan> schreef in bericht
news:uj3165-0sh.ln1@a62-251-88-195.adsl.xs4all.nl...
>
>
> see: http://dev.mysql.com/doc/refman/5.0/en/time.html
> 9.3.2. The TIME Type
> MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or
> 'HHH:MM:SS' format for large hours values). TIME values may range from
> '-838:59:59' to '838:59:59'. The hours part may be so large because the
> TIME type can be used not only to represent a time of day (which must be
> less than 24 hours), but also elapsed time or a time interval between two
> events (which may be much greater than 24 hours, or even negative).
>
>

mysql> desc tijd;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| t | time | YES | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into tijd values('15:00');

i repeated the insert 66 times;

mysql> select sec_to_time(sum(time_to_sec(t))), count(t) from tijd;
+----------------------------------+----------+
| sec_to_time(sum(time_to_sec(t))) | count(t) |
+----------------------------------+----------+
| 990:00:00 | 66 |
+----------------------------------+----------+
1 row in set (0.00 sec)

WHAT?, 990 hours, this is not what it says in the manual....

mysql> show variables like 'version';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| version | 5.0.26-Max-log |
+---------------+----------------+
1 row in set (0.00 sec)

On Wed, 16 Jan 2008 11:57:33 +0100, "Luuk" <luuk@invalid.lan> wrote:

>
>"Luuk" <luuk@invalid.lan> schreef in bericht
>news:uj3165-0sh.ln1@a62-251-88-195.adsl.xs4all.nl...
>>
>>
>> see: http://dev.mysql.com/doc/refman/5.0/en/time.html
>> 9.3.2. The TIME Type
>> MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or
>> 'HHH:MM:SS' format for large hours values). TIME values may range from
>> '-838:59:59' to '838:59:59'. The hours part may be so large because the
>> TIME type can be used not only to represent a time of day (which must be
>> less than 24 hours), but also elapsed time or a time interval between two
>> events (which may be much greater than 24 hours, or even negative).
>>
>>
>
>mysql> desc tijd;
>+-------+------+------+-----+---------+-------+
>| Field | Type | Null | Key | Default | Extra |
>+-------+------+------+-----+---------+-------+
>| t | time | YES | | NULL | |
>+-------+------+------+-----+---------+-------+
>1 row in set (0.00 sec)
>
>mysql> insert into tijd values('15:00');
>
>i repeated the insert 66 times;
>
>mysql> select sec_to_time(sum(time_to_sec(t))), count(t) from tijd;
>+----------------------------------+----------+
>| sec_to_time(sum(time_to_sec(t))) | count(t) |
>+----------------------------------+----------+
>| 990:00:00 | 66 |
>+----------------------------------+----------+
>1 row in set (0.00 sec)
>
>WHAT?, 990 hours, this is not what it says in the manual....
>
>mysql> show variables like 'version';
>+---------------+----------------+
>| Variable_name | Value |
>+---------------+----------------+
>| version | 5.0.26-Max-log |
>+---------------+----------------+
>1 row in set (0.00 sec)
>
PhP MyAdmin at my webhost indicates a MySQL client version of 4.1.22
However, I use XAMPP for windows on my home PC and the local PhP
MyAdmin shows an SQL client version of 5.0.45. This version also
maxes out at 838:59:59

Do you have any suggestions for another way to compute the total?

"Call Me Tom" <Noone@nowhere.net> schreef in bericht
news:hodto39gts1l6ua35n302mrsjdi7qoqnbp@4ax.com...
> On Wed, 16 Jan 2008 11:57:33 +0100, "Luuk" <luuk@invalid.lan> wrote:
>
>>
>>"Luuk" <luuk@invalid.lan> schreef in bericht
>>news:uj3165-0sh.ln1@a62-251-88-195.adsl.xs4all.nl...
>>>
>>>
>>> see: http://dev.mysql.com/doc/refman/5.0/en/time.html
>>> 9.3.2. The TIME Type
>>> MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or
>>> 'HHH:MM:SS' format for large hours values). TIME values may range from
>>> '-838:59:59' to '838:59:59'. The hours part may be so large because the
>>> TIME type can be used not only to represent a time of day (which must be
>>> less than 24 hours), but also elapsed time or a time interval between
>>> two
>>> events (which may be much greater than 24 hours, or even negative).
>>>
>>>
>>
>>mysql> desc tijd;
>>+-------+------+------+-----+---------+-------+
>>| Field | Type | Null | Key | Default | Extra |
>>+-------+------+------+-----+---------+-------+
>>| t | time | YES | | NULL | |
>>+-------+------+------+-----+---------+-------+
>>1 row in set (0.00 sec)
>>
>>mysql> insert into tijd values('15:00');
>>
>>i repeated the insert 66 times;
>>
>>mysql> select sec_to_time(sum(time_to_sec(t))), count(t) from tijd;
>>+----------------------------------+----------+
>>| sec_to_time(sum(time_to_sec(t))) | count(t) |
>>+----------------------------------+----------+
>>| 990:00:00 | 66 |
>>+----------------------------------+----------+
>>1 row in set (0.00 sec)
>>
>>WHAT?, 990 hours, this is not what it says in the manual....
>>
>>mysql> show variables like 'version';
>>+---------------+----------------+
>>| Variable_name | Value |
>>+---------------+----------------+
>>| version | 5.0.26-Max-log |
>>+---------------+----------------+
>>1 row in set (0.00 sec)
>>
> PhP MyAdmin at my webhost indicates a MySQL client version of 4.1.22
> However, I use XAMPP for windows on my home PC and the local PhP
> MyAdmin shows an SQL client version of 5.0.45. This version also
> maxes out at 838:59:59
>
> Do you have any suggestions for another way to compute the total?
>


you can leave out the sec_to_time(), than you will have the number of
seconds.
if you divde this by 3600, you get the number of hours..

or, you can upgrade to version 5.0 and write a nice function for this...


--
Luuk

"Luuk" <luuk@invalid.lan> schreef in bericht
news:rh4165-86i.ln1@a62-251-88-195.adsl.xs4all.nl...
>
> "Luuk" <luuk@invalid.lan> schreef in bericht
> news:uj3165-0sh.ln1@a62-251-88-195.adsl.xs4all.nl...
>>
>>
>> see: http://dev.mysql.com/doc/refman/5.0/en/time.html
>> 9.3.2. The TIME Type
>> MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or
>> 'HHH:MM:SS' format for large hours values). TIME values may range from
>> '-838:59:59' to '838:59:59'. The hours part may be so large because the
>> TIME type can be used not only to represent a time of day (which must be
>> less than 24 hours), but also elapsed time or a time interval between two
>> events (which may be much greater than 24 hours, or even negative).
>>
>>
>
> mysql> desc tijd;
> +-------+------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+------+------+-----+---------+-------+
> | t | time | YES | | NULL | |
> +-------+------+------+-----+---------+-------+
> 1 row in set (0.00 sec)
>
> mysql> insert into tijd values('15:00');
>
> i repeated the insert 66 times;
>
> mysql> select sec_to_time(sum(time_to_sec(t))), count(t) from tijd;
> +----------------------------------+----------+
> | sec_to_time(sum(time_to_sec(t))) | count(t) |
> +----------------------------------+----------+
> | 990:00:00 | 66 |
> +----------------------------------+----------+
> 1 row in set (0.00 sec)
>
> WHAT?, 990 hours, this is not what it says in the manual....
>
> mysql> show variables like 'version';
> +---------------+----------------+
> | Variable_name | Value |
> +---------------+----------------+
> | version | 5.0.26-Max-log |
> +---------------+----------------+
> 1 row in set (0.00 sec)
>
>

mysql> select sec_to_time(sum(time_to_sec(t))) from tijd;
+----------------------------------+
| sec_to_time(sum(time_to_sec(t))) |
+----------------------------------+
| 838:59:59 |
+----------------------------------+
1 row in set, 1 warning (0.02 sec)

mysql> show variables like 'version';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| version | 5.0.45-community-nt |
+---------------+---------------------+
1 row in set (0.00 sec)

so, on this version it works like in the manual.......


--
Luuk