Grouping by month or week

[lxndr]

I have a table that contains amongst other things the following columns:

 

comment:  text

date:  int(10)

 

The date is a unix timestamp and what I'd like to be able to do is run a MySQL query to show the number of comments made each week or each month so that the output might be something like:

 

January 2009:    345

February 2009:  384

 

etc. etc.

 

I'm sure there's a way to do it with a single SQL query but have so far failed to get it working.  Any help much appreciated.

 

___

 

[kickstart]

Hi

 

Try something like this

 

SELECT FROM_UNIXTIME(datefield, '%Y %M'), COUNT(comment)
FROM sometable
GROUP BY UNIXTIME(datefield, '%Y %M')

 

However be aware that you will need to play around to order it correctly (probably best done by bringing back the date column twice, once in a numeric format to order it by).

 

All the best

 

Keith

[lxndr]

Hi

 

Try something like this

 

SELECT FROM_UNIXTIME(datefield, '%Y %M'), COUNT(comment)
FROM sometable
GROUP BY UNIXTIME(datefield, '%Y %M')

 

However be aware that you will need to play around to order it correctly (probably best done by bringing back the date column twice, once in a numeric format to order it by).

 

All the best

 

Keith

 

Hi Keith,

 

Many thanks for the suggestion but when I try that query I get an error message saying function UNIXTIME does not exist?

 

Best wishes,

 

Ian

 

__

 

[kickstart]

Hi

 

Err. Aliens abducted some of my typing. Well thats my excuse anyway.

 

In the GROUP BY it should be FROM_UNIXTIME.

 

All the best

 

Keith

[lxndr]

Hi

 

Err. Aliens abducted some of my typing. Well thats my excuse anyway.

 

In the GROUP BY it should be FROM_UNIXTIME.

 

All the best

 

Keith

 

Hehe, thanks, that did the trick, your help much appreciated!

 

Best wishes,

 

Ian