SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    very simple datetime question

    i'm working on a news application and I want it to only display news articles that were posted in the last month (last 30 days). My query to the database returns a datetime type, but how can i use this to determine if the article is more than 30 days old?

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i got a better idea -- instead of pulling all the articles in the database and then filtering them in php, why not just ask the database to send over only the ones you want?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2008
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use substr() to remove all but the month day and year. Then use it again three times to create $month, $day, $year. Then you have your strings to run equations through to see if it should be displayed. There may be a feature built in to php or mysql somewhere to do this work for you, but I have not yet looked in to this.

  4. #4
    SitePoint Zealot
    Join Date
    Nov 2008
    Posts
    172
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...or what he said... lol.

  5. #5
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that would be acceptable. What would the sql look like to retrieve only those articles if i have a field articleDate (datetime)?
    Sorry my sql, is rusty and i'm blanking on how to accomplish this.

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT foo
         , bar
      FROM atable
     WHERE articleDate > CURRENT_TIMESTAMP - INTERVAL 30 DAY
    ORDER
        BY articleDate DESC
    The order by is optional.

    Having an index on articleDate could be very beneficial for performance.

    You might want to also use limit, in case there's a maximum number of rows you want.

  7. #7
    SitePoint Addict
    Join Date
    May 2008
    Location
    Missouri, USA
    Posts
    273
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crmalibu View Post
    Code:
    SELECT foo
         , bar
      FROM atable
     WHERE articleDate > CURRENT_TIMESTAMP - INTERVAL 30 DAY
    ORDER
        BY articleDate DESC
    That makes sense but i'm not very good with sql. How would I select just the articles where the dates fell in the last month?

    For example today is July 29th. I would want to only get articles that were posted in June.

    I'm probably way off but i figured if i'm asking for your help i might as well try to do it myself:

    Code:
    $interval = date('j');
    $year = date('Y');
    $month = date('n');
    $lastmonth = '';
    if($month == 1){
    $lastmonth = 12;
    }
    else{
    $lastmonth = $month - 1;
    }
    $num = cal_days_in_month(CAL_GREGORIAN, $lastmonth, $year) ; 
    $firstDayLastMonth = $interval + $lastmonth;
    
    SELECT foo
         , bar
      FROM atable
     WHERE articleDate > CURRENT_TIMESTAMP - INTERVAL $firstDayLastMonth DAY
     AND articleDate <  CURRENT_TIMESTAMP - INTERVAL $interval DAY
    ORDER
        BY articleDate DESC
    I've not tested this yet, but surely there is a better way to do it.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by bar338 View Post
    ... surely there is a better way to do it.
    yes, there is, but i don't do php so i cannot tell you how to come up with this

    but here is what you need to generate for June --
    Code:
     WHERE articleDate >= '2009-06-01'
       AND articleDate  < '2009-07-01'
    in other words, use php to figure out the first of the month you want, and the first of the following month

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think, this should be reliable
    Code:
     WHERE articleDate >= CONCAT(SUBSTRING(CURRENT_DATE - INTERVAL 1 MONTH, 1, 8), '01')
       AND articleDate < CONCAT(SUBSTRING(CURRENT_DATE, 1, 8), '01')

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by crmalibu View Post
    I think, this should be reliable
    only if the default display date format string is what you hope it is

    also, conversion of dates to strings and back to dates is notoriously inefficient

    the equivalent approach using date arithmetic would be
    Code:
     WHERE articleDate >= CURRENT_DATE 
                           - INTERVAL EXTRACT(DAY FROM CURRENT_DATE)-1 DAY
                           - INTERVAL 1 MONTH
       AND articleDate  < CURRENT_DATE 
                           - INTERVAL EXTRACT(DAY FROM CURRENT_DATE)-1 DAY
    however, i still think it'd be better to calculate the dates in php

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    @php.net Salathe's Avatar
    Join Date
    Dec 2004
    Location
    Edinburgh
    Posts
    1,397
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, there is, but i don't do php so i cannot tell you how to come up with this
    There are a plethora of ways to generate and format dates in PHP, here's one.

    PHP Code:
    $sqlf  "SELECT foo, bar FROM atable WHERE articleDate >= '%s' AND articleDate < '%s'";
    $past  date('Y-m-d'strtotime('-30 days'));
    $today date('Y-m-d');
    $sql   sprintf($sqlf$past$today);

    // SELECT foo, bar FROM atable WHERE articleDate >= '2009-06-30' AND articleDate < '2009-07-30'
    echo $sql
    Salathe
    Software Developer and PHP Manual Author.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's nice, salathe, but the dates are wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    @php.net Salathe's Avatar
    Join Date
    Dec 2004
    Location
    Edinburgh
    Posts
    1,397
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that's nice, salathe, but the dates are wrong
    My mistake, I was just going off of the first post:
    Quote Originally Posted by bar338 View Post
    only display news articles that were posted in the last month (last 30 days). how can i use this to determine if the article is more than 30 days old?
    To get the days in June (the last full month before today):
    PHP Code:
    $sqlf  "SELECT foo, bar FROM atable WHERE articleDate >= '%s' AND articleDate < '%s'";
    $first date('Y-m-d'strtotime('first day of last month'));
    $last  date('Y-m-d'strtotime('last day of last month'));
    $sql   sprintf($sqlf$first$last);

    // SELECT foo, bar FROM atable WHERE articleDate >= '2009-06-01' AND articleDate < '2009-06-30'
    echo $sql
    Salathe
    Software Developer and PHP Manual Author.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    still wrong

    ... WHERE articleDate >= '2009-06-01' AND articleDate < '2009-06-30'

    why exclude june 30th??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    @php.net Salathe's Avatar
    Join Date
    Dec 2004
    Location
    Edinburgh
    Posts
    1,397
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    I should really stop copy/pasting bits and pieces. I stole your SQL from seven posts up. Either add an = to the articleDate < or use "first day of month".
    Salathe
    Software Developer and PHP Manual Author.

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the upper end of the range test must be left open, i.e. using less-than, and not less-than-or-equal

    if you were to do this --
    Code:
     WHERE articleDate >= '2009-06-01'
       AND articleDate <= '2009-06-30'
    then you would miss all of the news items posted on june 30th except those which by sheer coincidence happened to be posted exactly at midnight on the morning of that day

    furthermore, changing it to this --
    Code:
     WHERE articleDate >= '2009-06-01'
       AND articleDate <= '2009-06-30 23:59:59'
    might appear to solve the problem but still allows for the possibility of missing rows, albeit not in mysql, which does not record datetimes to a fraction of a second (other database systems do), and not likely for a news article application

    it's a lot simpler to go with the best practice approach of
    Code:
     WHERE articleDate >= '2009-06-01'
       AND articleDate  < '2009-07-01'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    @php.net Salathe's Avatar
    Join Date
    Dec 2004
    Location
    Edinburgh
    Posts
    1,397
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    In that case, just change my $last to first day of month
    Salathe
    Software Developer and PHP Manual Author.


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
  •