Date diffrence

[rubbersoul]

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.

[2metre]

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.

[2metre]

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

[rubbersoul]

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!

[Wouter]

remove the word

round

...

"rubbersoul" <rg*****@vacv.com> wrote in message
news:f0**************************@posting.google.c om...
: 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!