MSQL and Time records
Can someone put me out of my current misery, despite having 1 MySQL book to leave through I cannot add these three records, I want to see the answer displayed as 01:30:00 (i.e sum of t1 - displayed in time format) which everyone understands as 1 hour 30 minutes :-
The field t1 is of type TIME
mysql> select * from time_val;
+----------+
| t1 |
+----------+
| 00:30:00 |
| 00:30:00 |
| 00:30:00 |
+----------+
3 rows in set (0.00 sec)
I don't understand this result at all:-
mysql> select sum(t1) from time_val;
+---------+
| sum(t1) |
+---------+
| 9000 |
+---------+
1 row in set (0.00 sec)
Here the records are displayed correctly in seconds:-
mysql> select time_to_sec(t1) from time_val;
+-----------------+
| time_to_sec(t1) |
+-----------------+
| 1800 |
| 1800 |
| 1800 |
+-----------------+
3 rows in set (0.00 sec)
Can anyone please put me on the right track?
***************************************************************************
I solved it for myself by changing the t1 data to a number and recording there only the minutes taken - no seconds - no hours just minutes.
Then the line below gives me what I now find acceptable for my result which is number of hours rounded.
select round(sum(t1*60)/3600) AS Hours_Spent from static;
Last edited by buttersoft; 03-23-2004 at 08:24 AM.
|