Date manipulation in MySQL

[nicky77]

Hi, I 've been trying to find a neat way of solving the following problem. I'm setting up a news section on a site, and I want to generate an archive menu as a list of months (and years), as a way of allowing users to view news items by month. In the database, my news items' dates are stored as unix timestamps. In my archive menu, I am generating the following url to filter by month and year, e.g news/may/2009. What I'm struggling to do at the moment is to take "May" and "2009" and retrieve all the items from the db which belong to this month and year. I've played about with this type of query, but can't get it quite right (the following isn't valid, but it hopefully demonstrates what i'm trying to do):

SELECT EXTRACT
(YEAR_MONTH 
FROM (
SELECT 
FROM_UNIXTIME(display_date,'%Y %D %M') 
FROM (`news`));

Any help would be appreciated, thanks.

[Dan Grossman]

There's a very straightforward way

SELECT somecols FROM `news` WHERE YEAR(display_date) = 2009 AND MONTH(display_date) = 5

Though by using functions the query can't take advantage of an index on display_date, if there is one.

[r937]

dan, you can't use YEAR(display_date) because display_date is just an integer

this should be efficient...

SET @year='2009', @month='May';
SELECT stuff
  FROM news
 WHERE display_date >= 
       UNIX_TIMESTAMP(STR_TO_DATE(CONCAT('01',@year,@month),'%d%Y%M'))
   AND display_date  < 
       UNIX_TIMESTAMP(STR_TO_DATE(CONCAT('01',@year,@month),'%d%Y%M')
                       + INTERVAL 1 MONTH)

[nicky77]

Thanks guys! I'd already taken Dan's reply and modified it as follows:

SELECT whatever
FROM `news`
WHERE MONTHNAME( FROM_UNIXTIME( display_date ) ) = 'October'
AND YEAR( FROM_UNIXTIME( display_date ) ) = '2009'

@r937, is your suggestion more efficient/reliable than the above?

[r937]

@r937, is your suggestion more efficient/reliable than the above?

more efficient, yes