0

I wrote SQL expression in format:

    select date1,date2,
cast ( Floor ( timestampdiff   (4, char (     timestamp (DATE2)-  
     timestamp (DATE1)))/1440) as char (3))||'days' ||' '  
 || cast ( Floor ( Mod ( timestampdiff (4, char(   timestamp (DATE2)-  
     timestamp (DATE1))) , 1440) / 60) as char (3)) ||'hours'

So I am having difference in minutes and then transforming it to days and hours. Example result will be:

DATE1                  DATE2                  DIFFERENCE
2012-01-01 10:00:00    2012-01-03 12:00     2 days 2 hours
2012-01-03 11:00:00    2012-01-03 12:00     0 days 1 hours

This is result which I am getting at the moment and it is ok. But now I want to have seperate result with AVERAGE value for difference. So for first row number of minutes is 3000. For second row number of minutes is 60. Average value is (3000+60)/2=1530 = 1 days 1 hours

So I want result in this format: average value: "1 days 1 hours"

I am using db2. Thank you

Stefke
  • 141
  • 8
  • 19
  • Solutions get insanely complicated unless your timestamps are **ALWAYS** UTC - you have to deal with both DST (days are not always 24 hours) and timezones. Do you specifically need this in the DB layer? This would probably be better handled in your application layer (depending on platform, it may be easier too). `TIMESTAMPDIFF()` only ever returns _estimates_ (30 days in a month only?), so it's probably not doing what you want. Also, the 'average' is going to end up different depending on the 'resolution' you perform it at - what happens when the difference is less than a minute? – Clockwork-Muse Jan 15 '13 at 17:18

1 Answers1

0

i dont know db2, but i did it for sql server, guess there wont be many changes

sqlFiddle

what you need is OVER() clause in aggregating function 

select *, DATEDIFF(hour, date1, date2)/24 as days
,DATEDIFF(hour, date1, date2)%24 as hours
,DATEDIFF(hour, date1, date2) as asdf,
AVG(DATEDIFF(hour, date1, date2)) over() as avgHours
from #temp
WKordos
  • 2,167
  • 1
  • 16
  • 15
  • Support on DB2 for `AVG()` as part of a windowing function is platform-dependent (doesn't work on the iSeries, for example). It also doesn't exactly have `DATEDIFF()` - there's `TIMESTAMPDIFF()`, which unfortunately returns an _estimate_ (including such things as months always being 30 days... You're also making one **terrible** mistake; that a day will always be 24 hours - your code is not DST safe. Besides that, in SQL Server, why would you ask for the date-diff in hours, then divide to get days (when you could have asked for days in the first place)? – Clockwork-Muse Jan 15 '13 at 17:05
  • "i dont know db2, but i did it for sql server" as stated, hes real problem was a missing over() clause in aggregation function and thats what i pointed to . Thanks for pointing DST problem didnt ever think about it – WKordos Jan 15 '13 at 23:21