I am trying to find the average for Fridays data for a period of 4 weeks,3 months,6 months and 12 months.I am new to db2 and wondering how to use dayofweek and weekofmonth in my query. I have a base query which uses the timestamp (friday's date 07:00:00) to timestamp (Saturdays date 07:00:00).Now i have to do the average for 4 weeks to begin with.Can someone please help.
Hey thank you so much for the info;it was very helpful.
Here's the query with the timestamp from 7:00 to 7:00;
select field-names from tablename 1 t1
where (dayofweek(date-time)=6 )
and date-time between timestamp('2013-09-1 07:00:00') and timestamp('2013-09-30 07:00:00')
I face an issue with this,everything works fine but the data i need is from Friday 7:00 am to Saturday 7:00 am(and its considered to be Friday's data),so when i use dayofweek=6 the data after 12:00 midnight will not be included.Ideally i need to add 7 hours from 12:00 midnight to the dayofweek.do you have any ideas for this?
so it should be like ; -- where (dayofweek(date-time)=6 ) **from 7am to (dayofweek(date-time=7 ) **until 7:00am --
date(timestamp_field) between date(friday_date) and date(saturday_date) and
((dayofweek(timestamp_field)=6 and hour(timestamp_field)>=7) or (dayofweek(timestamp_field)=7 and hour(timestamp_field)<7))
It doesn't solve the question how to get data from last 4 Fridays but gives you data from Friday 7 am to Saturday 7 am.
select some_cols, avg(some_amt)
where timestamp_field >= current timestamp - 29 days
group by some_cols
where timestamp-field between timestamp('2012-09-01 07:00:00') and timestamp('2013-09-30 07:00:00')
and ((dayofweek(timestamp-field)=6 and hour(timestamp-field)>=7) or (dayofweek(timestamp-field)=7 and hour(timestamp-field)<7))