updatedDate within the Last 7 days?

7 posts by 2 authors in: Forums > CMS Builder
Last Post: April 14, 2015   (RSS)

By Perchpole - April 10, 2015

Hello, All -

I want to put together a simple mechanism to flag-up (category) pages which have been updated during the past week. I need to work out how to test the various factors!

In broadly simple terms (I think) I need to test whether the category['updatedDate'] falls within the period between now and 7 days ago. If so, return this category.

Does that make sense?

Perhaps I could do it with a query?

Any thoughts would be most welcome.

Thanks,

:0/

Perchpole

By gkornbluth - April 10, 2015

Hi Perch,

Here's a recipe from The CMSB Cookbook http://www.thecmsbcookbook.com that might give you some ideas.

LISTING RECORDS WITHIN A DATE RANGE

Here’s one that came in extremely handy. 

I had an organization that had events that spanned a period of time and a special reception somewhere within those
dates.

My client wanted to list all of the events on their list page, but wanted to make a special list at the top of the page
for those events with receptions that were going to occur within the upcoming week.

Thanks to Dave Edis of Interactive Tools (again) who freely admits that date math makes his head hurt, we came up with
the following. 

First I created a date field in my ‘date_compare_test’ multi record editor called ‘reception_date’.

Then came the task of creating a list viewer that would list only the events I wanted in the appropriate groups.

Dave came up with the idea of putting 2 viewers on my list viewer page and using 2 different ‘where’ statements in
the get records calls to separate the content that could show in each area.

Here's the 'where' statement that we used to show reception_ dates for the upcoming week, as you suggested.


    
 'where' => '(NOW() + INTERVAL 7 DAY) >= reception_date AND reception_date >= TIMESTAMP(CURDATE(), "00:00:00")',



And here's the 'where' statement that we used to show all the other events (and leave out those already listed)



 'where' => 'reception_date <= TIMESTAMP(NOW()) OR reception_date >= TIMESTAMP(NOW()+ INTERVAL 7 DAY)',



So here’s how the complete viewer code looked.



 <!– Start of first list viewer code–>

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
  
  require_once "/hsphere/local/home/apbcweb/artistsofpalmbeachcounty.org/cmsAdmin/lib/viewer_functions.php";

  list($date_compare_testRecords, $date_compare_testMetaData) = getRecords(array(
'Table name'   => 'date_compare_test',
'where' => '(NOW() + INTERVAL 7 DAY) >= reception_date AND reception_date >= TIMESTAMP(CURDATE(), "00:00:00")',
  ));
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 <head>
  <title></title>
  <meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
 
<hr />

EVENTS WITH RECEPTIONS TODAY THROUGH 7 DAYS IN THE FUTURE<br /><br />
<?php foreach ($date_compare_testRecords as $record): ?> 
Title: <?php echo $record['title'] ?><br />
The Reception Date is <?php echo date("D, M jS, Y g:i a", strtotime($record['reception_date'])) ?> <br />
<?php endforeach ?> 
<hr />
  
 <!– Start of second list viewer code–>

<?php

  list($date_compare_testRecords, $date_compare_testMetaData) = getRecords(array(
    'Table name'   => 'date_compare_test',
  'where' => 'reception_date < TIMESTAMP(CURDATE(), "00:00:00") OR reception_date > TIMESTAMP(NOW()+ INTERVAL 7 DAY)',
 ));
?>

 EVENTS WITH RECEPTIONS BEFORE TODAY OR MORE THAN 7 DAYS IN THE FUTURE<br /><br />
  <?php foreach ($date_compare_testRecords as $record): ?> 
      
      Title: <?php echo $record['title'] ?><br />
      The Reception Date is <?php echo date("D, M jS, Y g:i a", strtotime($record['reception_date'])) ?> <br />
<?php endforeach ?> 
  
</body>
</html>



Dave explained that for date queries, 

“The first step is to figure out if you need to do it in MySQL or PHP. If you are able to do it in MySQL it's often
simpler.

You can find a list of MySQL date/time functions here:

       http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

And here's some examples:

NOW() - Gives you the current date and time: 2010-03-01 09:41:50
(NOW() + INTERVAL 7 DAY) - Get date/time 7 days from now: 2010-02-22 09:45:25
CURDATE() - Gives you the current date only: 2010-03-01
TIMESTAMP() - Format value as date/time, or if two values, add them together
TIMESTAMP(CURDATE()) - Get date/time at beginning of day: 2010-03-01 00:00:00
TIMESTAMP(CURDATE(), “23:59:59") - Get date/time at end of day 2010-03-01 23:59:59

And you can test all these with the MySQL Console Plugin by entering SELECT followed by the function. So: SELECT NOW()
in the Mysql Console returns: 2010-03-01 09:41:50

So the first step is to figure out the values you want to compare. My guess is you want:
The date 7 days from now: (NOW() + INTERVAL 7 DAY)
The reception date: reception_date
The date at the end of today: TIMESTAMP(CURDATE(), “23:59:59")

If you write it out in English first it's way easier:
- If the reception_date is 7 days or less from now
- AND the reception_date hasn't already passed

I like to arrange my code so if reads like a time range with the test date in the middle like this:



start_date >= test_date AND test_date >= end_date



So that would be:



(NOW() + INTERVAL 7 DAY) >= reception_date AND reception_date >= TIMESTAMP(CURDATE(), “00:00:00") 



Now I understand why Dave says date math makes his head hurt.

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Perchpole - April 10, 2015

Hi, Jerry -

Thanks for this. I knew there was a recipe similar to what I wanted in the cookbook. The only issue is that I am dealing with Category pages - so can't use a "Where" clause!

That's why I thought a straight query might be the best approach.

:0)

Perch

By gkornbluth - April 13, 2015

Hi Perch,

Any luck?

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Perchpole - April 13, 2015

Hi, Jerry -

The answer is yes - but its pretty basic...

<?php $lastWeek = strtotime('-7 days'); ?>

<?php $updateArray = array(); ?>
<?php foreach($category as $updated): ?>
<?php if(strtotime($updated['updatedDate']) < $lastWeek) {continue;} ?>
<?php $updateArray[] = $updated ?>
<?php endforeach ?>

That gives you a new array containing only those category records which were updated in the last 7 days.

I'm sure there's a smarter way of doing it with a query - but this is fit for purpose right now.

The reason I need it is because one site does not have "new" news stories. They simply update their existing static pages. This bit of code will alert visitors to the fact that certain pages have been changed.

:0)

Perch

By gkornbluth - April 14, 2015

Thanks Perch,

I've found that nothing is "basic" until you've figured it out, so thanks for sharing this.

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php