SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Finding the date 30 days ago...?

    I have some news articles in my mysql db. I want to display all the articles posted up in the past month. I know how to get todays date - date("d m Y"), but how can I find out the date a month ago? Once I have that, I guess the sql will be something like "SELECT * FROM news WHERE newsDate >= {whatever 30 days ago was}"
    The field is a TimeStamp with default value of current date. Its stored in this format - 2008-11-10 12:10:02
    Thanks

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://dev.mysql.com/doc/refman/5.0/...functions.html
    Code:
    select *
    from news
    where date(newsDate) >= date(now()-interval 30 day)
    or if you want to give literal dates
    Code:
    select *
    from news
    where date(newsDate) between '2008-10-10' and '2008-11-10'
    The db convert automatically your text input of dates to datetime, so no need to handle this yourself.

  3. #3
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And if you want to get the 30 days ago date in PHP:
    PHP Code:
    echo date('Y-m-d'strtotime('-30 days')); 
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2008
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. That looks easy.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sensory007 View Post
    The field is a TimeStamp with default value of current date. Its stored in this format - 2008-11-10 12:10:02
    actually, it isn't

    internal storage formats are completely unrecognizable (if you were ever able to see them, which you can only do by reading the actual physical storage files yourself)

    also, "past 30 days" is not the same as "past month"

    it is the difference between 2008-10-13 to 2008-11-13 and 2008-10-01 to 2008-10-31

    tripy, don't forget that if you do this --
    Code:
    date(newsDate) >= date(now()-interval 30 day)
    then by putting a function on the column, you're forcing the optimizer into a table scan because it can't use an index on the column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    tripy, don't forget that if you do this --
    Code:

    date(newsDate) >= date(now()-interval 30 day)

    then by putting a function on the column, you're forcing the optimizer into a table scan because it can't use an index on the column
    Now that you say it, it struck me.
    cause now()-interval is not... what's the term now... predictable ? Deterministic ?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    actually, date(now()-interval 30 day) will be evaluated only once, before the query executes

    the problem is with date(newsDate) -- the function applied to the column is what causes the optimizer to abandon using the index on that column
    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
  •