Jump to content


This topic is now archived and is closed to further replies.


need help with UNIX_TIMESTAMP() with GROUP BY

Recommended Posts


I have this query:
[code]$query = "SELECT UNIX_TIMESTAMP(date) AS udate, blog.*, COUNT(blog_id) AS blog_count FROM blog GROUP BY date ORDER BY date DESC";[/code]

this statement is meant to create a list of archives like this...

March 2006 (7)
February 2006 (3)
January 2006 (2)

etc, but since the date is in datetime format --> 0000-00-00 00:00:00 it can't GROUP BY properly, I used to use another field that would display 'may 2006' etc in the news marked as that month (which worked fine) but I want to be able to group them using 'date'.

I'm not sure how to go about this, so that it uses the first 0000-00.. etc as a way of grouping, but I'm not sure if this is possible and if it is, how I do it.

could someone please help me?

thanks in advance.


Share this post

Link to post
Share on other sites
You may want to try grouping by "EXTRACT( YEAR_MONTH FROM date )"; obviously, you could use a pretty DATE_FORMAT() call for the output itself. Incidentally, date is a reserved keyword, so be careful.

Hope that helps.

Share this post

Link to post
Share on other sites
thanks alot, I managed to get it working with DATE_FORMAT().

thanks again! [img src=\"style_emoticons/[#EMO_DIR#]/laugh.gif\" style=\"vertical-align:middle\" emoid=\":laugh:\" border=\"0\" alt=\"laugh.gif\" /]

Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.