Getting month from date

Databases
1

I have a blog that I’m trying to customize, and I’m trying to extract the month from each entry date. The entry date format looks like this:

1282078080

Which (using the php date function) translates to Tuesday, August 17, 2010.

But I need the month at the query level, because I want to group older posts by month. I’ve tried doing this:


SELECT  blog_id
			, category_id
			, entry_author_id
			, entry_id
			, entry_name
			, entry_date
			, month(entry_date) as postmonth
		FROM blog_entries
		WHERE entry_author_id = 1
		AND entry_status = 'published'
		ORDER BY entry_date DESC
		LIMIT 5, 15
		");

But I get a blank. Not sure why php can pull a date from that string but the month() function can’t. I also tried monthname() with the same (lack of) results.

What am I doing wrong?

2

That did it! I ended up using the second option, with the %M flag.

Thanks!

3

you’re using a date function on an integer – you have to convert the integer to a date first, and then there are 2 ways of extracting the month

MONTH(FROM_UNIXTIME(entry_date))

or

FROM_UNIXTIME(entry_date,‘%c’)