On Mon, 26 Nov 2001, Julio Faerman wrote:
> Is there anything like a "to_hours()" function ?
> I need my query to to calculate how much time there is between two time
> values....
Julio,
ok, we are trying to help you, but please make our lives a little bit
easier and tell us exactly what you want to do. I guess you want a
difference between two time values and I suppose you mean columns of type
TIME. (Using DATETIME would offer a better range).
Let's make a table:
use test;
create table timetest (
number int auto_increment primary key,
start time,
stop time
);
Now we populate this table with some data:
INSERT INTO timetest VALUES
(NULL,'08:03:02','16:56:28'),
(NULL,'19:03:02','11:56:28');
We have:
mysql> select * from timetest;
+--------+----------+----------+
| number | start | stop |
+--------+----------+----------+
| 1 | 08:03:02 | 16:56:28 |
| 2 | 19:03:02 | 11:56:28 |
+--------+----------+----------+
2 rows in set (0.00 sec)
and what you probably want is:
mysql> select TIME_FORMAT(stop-start,'%T') AS elapsed from timetest;
+----------+
| elapsed |
+----------+
| 08:53:26 |
| NULL |
+----------+
2 rows in set (0.00 sec)
As you can see this works for positive intervals only, but we can
do:
mysql> SELECT SEC_TO_TIME(ABS(TIME_TO_SEC(stop)-TIME_TO_SEC(start)))
AS elapsed FROM timetest;
+----------+
| elapsed |
+----------+
| 08:53:26 |
| 07:06:34 |
+----------+
2 rows in set (0.00 sec)
The result may be formatted in any desired way using the
TIME_FORMAT(time,format) function.
mysql> SELECT
TIME_FORMAT(SEC_TO_TIME(ABS(TIME_TO_SEC(stop)-TIME_TO_SEC(start))),
'%H hours %i:%s') AS elapsed FROM timetest;
+----------------+
| elapsed |
+----------------+
| 08 hours 53:26 |
| 07 hours 06:34 |
+----------------+
2 rows in set (0.00 sec)
I hope that's what you are looking for.
Cheers,
Thomas
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
