MySql Date Calculation Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

MySql Date Calculation Query

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySql Date Calculation Query

Post 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
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: MySql Date Calculation Query

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySql Date Calculation Query

Post 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.
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: MySql Date Calculation Query

Post 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
Post Reply