SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Most Efficient Way of Selecting Row by Month/Year?

    I have a page where I only select the rows from my database that are in a particular month/year. The variable sent to each page is in YYYY-MM format.

    To do my select query, I just did:

    DATE_FORMAT(video.date,'%Y-%m') = '2009-12'

    It works fine, I'm just making sure that this isn't too resource intensive. Is there a better way that uses less resources in the query?

    THx
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, there's a better way

    1. make sure that video.date is indexed

    2. use a range test --
    Code:
    WHERE video_date >= '2009-12-01'
      AND video.date  < '2010-01-01'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok,

    That's what I was afraid of. So I'd have to take the YYYY-MM variable, add an "01" as the day and run it through some PHP coding to figure out the plus one on the month.

    If you know I quick solution for adding a month I'm all ears.

    Cheers
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by casbboy View Post
    If you know I quick solution for adding a month I'm all ears.
    adding a month in mysql or in php?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I ended up going with PHP.

    PHP Code:
    $dateday $date .'-01';
    $datefuture date("Y-m-d"strtotime(date("Y-m-d"strtotime($dateday)) . " +1 month")); 
    That gives me my one-month span for the query.

    Cheers
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  6. #6
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just to clarify:

    WHERE video_date >= '2009-12-01'
    AND video.date < '2010-01-01'

    That date format works properly with datetime columns, correct? It's working, just want to make sure it isn't missing anything from the first day due to time.

    Cheers
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.


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
  •