very simple datetime question

[bar338]

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.

[r937]

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?

[Kyle W]

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.

[Kyle W]

...or what he said... lol.

[bar338]

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.

[crmalibu]

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.

[bar338]

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:

$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.

[r937]

... 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 --

 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

[crmalibu]

I think, this should be reliable

 WHERE articleDate >= CONCAT(SUBSTRING(CURRENT_DATE - INTERVAL 1 MONTH, 1, 8), '01')
   AND articleDate < CONCAT(SUBSTRING(CURRENT_DATE, 1, 8), '01')

[r937]

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

 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

[Salathe]

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.

$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; 

[r937]

that's nice, salathe, but the dates are wrong

[Salathe]

that's nice, salathe, but the dates are wrong

My mistake, I was just going off of the first 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):

$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; 

[r937]

still wrong

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

why exclude june 30th??

[Salathe]

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".

[r937]

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

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

 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

 WHERE articleDate >= '2009-06-01'
   AND articleDate  < '2009-07-01'

[Salathe]

In that case, just change my $last to first day of month