2metre <2m****@xxxhersham.net> wrote in message news:<cm**********@titan.btinternet.com>...
2metre wrote: rubbersoul wrote:
I have a table with 3 colums:
Date (datetime)
End (datetime)
Sys_id (INT)
What I need to do is write a query that will do a calculation and
return the diffrence in hours between date and end. I though that
maybe a DATE_SUB would work but I can't seem to get it to do as I
want. Hope someone can help.
Subtract the UNIX timestamps and that will give you the difference in
seconds.
or for hours rounded to the nearest.. (have tested this!)
select
Sys_id,
round((UNIX_TIMESTAMP(End)-UNIX_TIMESTAMP(Date))/3600)
from
tablename
This works but dosen't return what I need.
----+---------------------+-------------+--------------------
16 | 2004-10-11 08:05:00 | Problem | 2004-10-11 09:00:00
16 | 2004-12-12 13:00:00 | Problem | 2004-12-14 15:00:00
I tried running your query and I got this:
+--------+---------------------------------------------------------+
| sys_id | round((unix_timestamp(end) -unix_timestamp(date))/3600) |
+--------+---------------------------------------------------------+
| 16 | 1 |
| 16 | 50 |
+--------+---------------------------------------------------------+
I know this is what I initally asked for but I need the first row to
come out as 0.55 for the minutes. Sorry....I don't know if this is as
easy as just removing the round, but I doubt it!