Group rows by day

[therealwesfoster]

How can I group rows by their day of the month? Example: craigslist.com

 

Table name: posts

Date column: post_date

 

How can I go about doing this? thanks

[Mchl]

What's the format you keep post_date in?

[therealwesfoster]

As a unix timestamp

[corbin]

If it were a datetime type it would be easier.

 

 

The first thing that comes to mind is:

 

 

SELECT * FROM posts GROUP BY DAY(FROM_UNIXTIME(post_date)), MONTH(FROM_UNIXTIME(post_date)), YEAR(FROM_UNIXTIME(post_date));

 

 

That would be HORRIBLY slow though.

[therealwesfoster]

Thanks, but we want to avoid "horribly slow". So if I were to change the column type to "datetime" it would be faster? How would I access the date using PHP and still be able to use date()?

 

Wes

[Mchl]

Use DATETIME or TIMESTAMP types to store datetime data.

You just need to reformat date() function so that it's compatible with mysql format.

[corbin]

http://lists.mysql.com/mysql/183242

 

 

 

I think that pretty much answers your questions.

 

 

Found it pretty interesting for me to read too.