adding datetime fields 1

[kaptlid]

I have a table with
col1 col2 both are datetime columns

I made a derived table to display
col1, col2, col2-col1 as difference

what I want to do now is add all the row values of difference together. The sum function butchers it and date_add does not seem to be for this.

Thanks,

 

[r937]

select sum(to_days(col1)
          -to_days(col2)) as total_diff
  from daTable

r937.com | rudy.ca

 

[kaptlid]

Maybe I did not make it clear what I am asking for. The above query gives me a value of zero.

Say I have a derived table that looks like this and has this data.

total col1 col2 timespent
0 2006-12-30 08:30:00 2006-12-30 15:45:00 07:15
0 2006-12-28 19:22:25 2006-12-28 19:27:46 00:05

how do I get it to display:
total col1 col2 timespent
07:20 2006-12-30 08:30:00 2006-12-30 15:45:00 07:15
07:20 2006-12-28 19:22:25 2006-12-28 19:27:46 00:05

The query I am using is:
SELECT sum(to_days(`out`)-to_days(`in`)) as total, `in`, `out`, left(timediff(`out`,`in`),5) as `time spent` FROM `table` group by `id`

 

[r937]

you want to show the sum at the same time as showing the detailed rows? and you want to GROUP BY id? why id?

SELECT (select sum(to_days(`out`)
                  -to_days(`in`))
          from `table`
         where id = foo.id )  as total
     ,  `in`
     , `out`
     , left(timediff(`out`,`in`),5) as `time spent` 
  FROM `table` as foo
group 
    by `id`

r937.com | rudy.ca

 

[kaptlid]

I could not figure out what to put in the group by clause...

SELECT (

SELECT sum( to_days( `out` ) - to_days( `in` ) )
FROM `table`
) AS total, `in` , `out` , left( timediff( `out` , `in` ) , 5 ) AS `time spent`
FROM `table` AS foo

I tried this I still get an answer of zero... i am using mysql 4.1

I also tried just the basic query:

SELECT sum( to_days( `out` ) - to_days( `in` ) )
FROM `table`

and still get a value of zero. Thanks

 

[r937]

what do you get for this:

SELECT to_days( `out` ) as days_out
, to_days( `in` ) as days_in
FROM `table`


r937.com | rudy.ca

 

[kaptlid]

days_out days_in
733038 733038
733040 733040

 

[r937]

ah, i think i see the problem

i'm sorry for suggesting the TO_DAYS function

clearly, you are not interested in the days different, but in the time difference

please try this --

SELECT sec_to_time(
       (select sum(unix_timestamp(`out`)
                  -unix_timestamp(`in`))
          from `table`
         where id = foo.id )
                  )   as total
     , `in`
     , `out`
     , left(timediff(`out`,`in`),5) as `time spent` 
  FROM `table` as foo
group 
    by id

r937.com | rudy.ca

 

[kaptlid]

Thank you that worked beautifully.