need query that groups dates by month

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
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

need query that groups dates by month

Post by rhecker »

I have a MySQL database of events. There might be several events each month. I can generate a list of the events, ordered by date, but what I can't figure out, is how to separate them by month.
In other words, I want to end up with a list like the following:

APRIL
4/3/2010 Beginning Yoga
4/6/2010 Advanced Yoga
4/20/2010 Flower arranging

MAY
5/7/2010 Introduction to Botony
5/9/2010 Advanced Yoga

I know how to retrieve the month names using date(F) but I don't know how to create the subgroups based on month. The dates are stored as Epoch values.
Any help with how to write this query appreciated.
User avatar
yacahuma
Forum Regular
Posts: 870
Joined: Sun Jul 01, 2007 7:11 am

Re: need query that groups dates by month

Post by yacahuma »

what will be the input , just the year

Or are you going to loop for year , month?
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: need query that groups dates by month

Post by rhecker »

I will need to use month and year so that January 2011 doesn't come before June 2010. Below is as far as I got with the code, but it doesn't work. I think I have two issues. Firstly, since the dates are stored as integers in the database, I can't use MySQL to group the startdate column by month and year, so I have to have PHP do it. Secondly, I just don't know how to write the sequence of loops that will result in the format I show in my first post. All ideas appreciated!

Code: Select all

$currentdate = date (U);
$getmonth = mysql_query ("select startdate from course WHERE startdate > $currentdate order by startdate");
while ($month = mysql_fetch_array($getmonth)){
$stopmonth = $monthtext;	
extract ($month);
$monthtext = date('F Y', $startdate);
if ($stopmonth != $monthtext) {
	echo "<h2>$monthtext</h2>";
}

$getdate = mysql_query ("select id, startdate, enddate from course WHERE startdate > $currentdate order by startdate");
while ($date = mysql_fetch_array($getdate)){
extract ($date);
$start = date('F', $startdate);
echo "ID: $id DATE: $start<br/>";
}}
User avatar
yacahuma
Forum Regular
Posts: 870
Joined: Sun Jul 01, 2007 7:11 am

Re: need query that groups dates by month

Post by yacahuma »

so you are not using mysql date??
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: need query that groups dates by month

Post by rhecker »

Right, I cannot use MySQL date because the dates are stored in Epoch format.
User avatar
yacahuma
Forum Regular
Posts: 870
Joined: Sun Jul 01, 2007 7:11 am

Re: need query that groups dates by month

Post by yacahuma »

Code: Select all

select FROM_UNIXTIME(date1),EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(date1)) 
from test WHERE EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(date1))=201004
rhecker
Forum Contributor
Posts: 178
Joined: Fri Jul 11, 2008 5:49 pm

Re: need query that groups dates by month

Post by rhecker »

That's great, thanks! Being able to work with the UNIX dates as dates in the queries is exactly what I really needed.
Post Reply