Page 1 of 1

MySql Date Calculation Query

Posted: Fri Dec 18, 2009 5:52 am
by Flashart
Hi everyone

I have what is probably a very simple problem but after an hour i am stumped on the syntax to use.

I am working with a database that holds date information in one column in the usual format, yyyy-mm-dd.

What I am trying to do is retrieve all records from today going back 6 months. What I have at the moment is;

Code: Select all

SELECT * from adgroup_data WHERE date >=DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH) order by date"
It works however it retrieves 6 months by day. What I need the query to do is go back 6 months from the start of this week and group the results by week. In essence:

1) Look at the current date
2) Find the Monday before this date
3) Retrieve 6 mths data from this date
4) Group that data into weeks (7 days)
5) Display only the summarised weekly totals for the data

I realise the database date column perhaps isn't the best in terms of format however I am having to work with this at the moment whilst I build a better DB!

I would appreciate any help you can give.

Kind regards
Peter

Re: MySql Date Calculation Query

Posted: Fri Dec 18, 2009 8:37 am
by Eran
In order to summarize the data, you need to group it somehow. Since you want it summarized by week, you can use the WEEK() function to accomplish that -
[sql]SELECT * FROM adgroup_data WHERE date >=DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH) GROUP BY WEEK(date)ORDER BY date[/sql]
http://dev.mysql.com/doc/refman/5.1/en/ ... ction_week

Re: MySql Date Calculation Query

Posted: Fri Dec 18, 2009 10:32 am
by Flashart
HI

Thanks for that. It doesn't seem to retrieve all records though? it seems to group by week which is fine (6 mths worth of weeks) and that is what i was after so that's great. It only returned 27 records? I appreciate this is difficult without seeing the data.

I was under (possibly incorrect) impression that the SELECT * would retrieve every record dating back 6 months, and then group each adgroup together whilst giving me the summed total.

I probably wasn't clear enough. I know what I want! Just my grasp of English is failing me!

Essentially I want the SQL to find each occurance of an adgroup over the past 6 mths (the data is held by day) and then add up all 7 days worth of the data and then spit out the total for week 1, week 2 etc etc.

To rephrase what I wrote earlier:

1) Look at the current date
2) Find the Monday before this date
3) Retrieve 6 mths data from this date for every adgroup
4) Group that data into weeks (7 days)
5) Display only the summarised weekly totals for each and every adgroup

Re: MySql Date Calculation Query

Posted: Fri Dec 18, 2009 10:42 am
by Eran
Yes, I understood what you said. As you described, it's difficult to see what the problem is without seeing the data. Possibly there is no data for some of the weeks, hence you aren't getting them all - it should get all the data for 6 months back from today.

Re: MySql Date Calculation Query

Posted: Fri Dec 18, 2009 11:04 am
by Flashart
There is definitely data as I can see it on a day by day basis.

You have given me a starting point (I had been playing about with groups etc so I was close but not close enough!) so thank you for that.

I just need to figure out why its only giving me 27 rows when it should give me a result for every adgroup for every week for the last 6 months, which would equate to over 2k rows...

Kind regards
Peter