| re: Date diffrence
2metre <2metre@xxxhersham.net> wrote in message news:<cmdr69$lo9$1@titan.btinternet.com>...[color=blue]
> 2metre wrote:[color=green]
> > rubbersoul wrote:
> >[color=darkred]
> >> 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.[/color]
> >
> > Subtract the UNIX timestamps and that will give you the difference in
> > seconds.[/color]
>
> or for hours rounded to the nearest.. (have tested this!)
>
> select
> Sys_id,
> round((UNIX_TIMESTAMP(End)-UNIX_TIMESTAMP(Date))/3600)
> from
> tablename[/color]
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!
| 
