SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Aug 2007
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Date manipulation in MySQL

    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):

    Code:
    SELECT EXTRACT
    (YEAR_MONTH 
    FROM (
    SELECT 
    FROM_UNIXTIME(display_date,'%Y %D %M') 
    FROM (`news`));
    Any help would be appreciated, thanks.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    There's a very straightforward way
    Code:
    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.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    dan, you can't use YEAR(display_date) because display_date is just an integer

    this should be efficient...
    Code:
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2007
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys! I'd already taken Dan's reply and modified it as follows:
    Code:
    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?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nicky77 View Post
    @r937, is your suggestion more efficient/reliable than the above?
    more efficient, yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •