calculating date differences

4 posts by 2 authors in: Forums > CMS Builder
Last Post: June 5, 2011   (RSS)

By Deborah - June 3, 2011

I am setting up an employee portal where we want to display the hire date AND how much time has passed for each hire date to current date of when the page is being viewed.

Example results (based on a current date being 06/03/2011):

$hire_date = 02/01/2000
result = 11 years, 3 months

$hire_date = 05/02/2011
result = 1 month, 1 day

$hire_date = 06/01/2011
result = 2 days

The calculations would have to take into account leap years, but need not be precise to the second.

Does anyone have a formula for arriving at the results shown?

Thanks in advance for any help.

~ Deborah

Re: [Deborah] calculating date differences

By gkornbluth - June 3, 2011

Hi Deborah,

I don't have an exact answer to your question, but here's one of the recipes that deals with comparing dates from my CMSB Cookbook thecmsbcookbook.com that should help to get you started:

COMPARING DATES
When combined with the “if” statement, this extremely useful concept can be used to show or hide fields based on a particular date, automatically archive records after a specified time period and perform many other functions.

Here’s the basic idea of how to compare a date field to today’s date on a list page. This example uses a date field called “opening_reception” in a multi record editor called “exhibitions”. The goal was to automatically hide the opening reception date after the date had passed.

First you’ll need a “foreach” statement to display each record.

<?php foreach ($exhibitionsRecords as $record): ?>

Since dates are normally expressed as months, days and years are hard to compare mathematically, the dates are converted to the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT)

Note that dates are referenced to local time as set in the CMSB “General Settings” information and not server time.

Dave suggested to first define a few variables and to use simple, descriptive names so that the process is easier to follow. Then set some rules for the comparisons. You can use mathematical operators like <, >, <= or >= between values to compare them in different ways.

<?php
$eventUnixTime = strtotime( $record['reception_date'] ); // seconds since 1970
$eventDateString = date("l, F jS", $eventUnixTime); // example format: Monday, June 1st
$currentUnixTime = time();
$currentDateString = date("l, F jS", $currentUnixTime);


$isEventToday = ($eventDateString == $currentDateString); // first comparison
$isEventOver = !$isEventToday && ($eventUnixTime < $currentUnixTime); // second comparison
$isFutureEvent = !$isEventOver && !$isEventToday; // third comparison
?>


Then it becomes a simple matter of comparing the variables

<?php if ($isFutureEvent): ?>
Opening Reception: <?php echo $eventDateString ?>

<?php elseif ($isEventToday): ?>
The Opening reception is today. Don't miss it!!

<?php else: ?>
Sorry, you missed the Opening Reception.

<?php endif; ?>

and don’t forget the endforeach statement to close your loop.

<?php endforeach ?>

Or, Let’s say you want to group a set of meetings by future and past dates

You could use:

<?php foreach ($general_meetingsRecords as $record): ?>
<?php
$eventUnixTime = strtotime( $record['date'] ); // seconds since 1970
$eventDateString = date("l, F jS", $eventUnixTime); // example format: Monday, June 1st
$currentUnixTime = time();
$currentDateString = date("l, F jS", $currentUnixTime);

$isEventToday = ($eventDateString == $currentDateString);
$isEventOver = !$isEventToday && ($eventUnixTime < $currentUnixTime);
$isFutureEvent = !$isEventOver && !$isEventToday;
?>

<?php if ($isEventToday): ?> <p>

<div align="center"class="heading-text-bold">TONIGHT'S MEETING:</div>
</p>
<br /> <span class="body-text-bold"><?php echo date("D, M jS, Y g:i a", strtotime($record['date'])) ?>
</span>
<br />
<div align="left" class="body-text"><?php echo $record['content'] ?></div>
<hr align="left" color="#A29DB2" width="100" /><br />
<?php endif; ?> <?php endforeach ?>
</div>
<p>

<div align="center"class="heading-text-bold">UPCOMING MEETINGS:</div>
</p>
<?php foreach ($general_meetingsRecords as $record): ?>

<?php
$eventUnixTime = strtotime( $record['date'] ); // seconds since 1970
$eventDateString = date("l, F jS", $eventUnixTime); // example format: Monday, June 1st
currentUnixTime = time();
$currentDateString = date("l, F jS", $currentUnixTime);

$isEventToday = ($eventDateString == $currentDateString);
$isEventOver = !$isEventToday && ($eventUnixTime < $currentUnixTime);
$isFutureEvent = !$isEventOver && !$isEventToday;
?>

<?php if ($isFutureEvent): ?>
<br />
<span class="body-text-bold"><?php echo date("D, M jS, Y g:i a", strtotime($record['date'])) ?></span> <br />

<div align="left" class="body-text"> <?php echo $record['content'] ?></div><hr align="left" color="#A29DB2" width="100" />
<?php endif; ?> <?php endforeach ?>
<br />
<p><div align="center" class="heading-text-bold">PAST MEETINGS:</div></p>

<?php foreach ($general_meetingsRecords as $record): ?>

<?php
$eventUnixTime = strtotime( $record['date'] ); // seconds since 1970
$eventDateString = date("l, F jS", $eventUnixTime); // example format: Monday, June 1st
$currentUnixTime = time();
$currentDateString = date("l, F jS", $currentUnixTime);

$isEventToday = ($eventDateString == $currentDateString);
$isEventOver = !$isEventToday && ($eventUnixTime < $currentUnixTime);
$isFutureEvent = !$isEventOver && !$isEventToday;
?>

<?php if ($isEventOver): ?>
<br />
<span class="body-text-bold"><?php echo date("D, M jS, Y g:i a", strtotime($record['date'])) ?></span> <br />


<div align="left" class="body-text"><?php echo $record['content'] ?></div><hr align="left" color="#A29DB2" width="100" />
<?php endif; ?> <?php endforeach ?>
</div>


Note that you have to redefine the variables for each "foreach" loop

If you want to compare other field values, just define more variables and compare those as well.

If you want to test for the values of the variable that you're using. Try something like this in your foreach loop:

<ul>
<li>Event date = <?php echo $eventDateString ?></li>
<li>Current date = <?php echo $currentDateString ?></li>
<li>$isEventToday = <?php echo $isEventToday ? "true" : "false" ?></li>
<li>$isEventOver = <?php echo $isEventOver ? "true" : "false" ?></li>
<li>$isFutureEvent = <?php echo $isFutureEvent ? "true" : "false" ?></li>
<li>$CurrentUnixTime extended = <?php echo date("l jS \of F Y h:i:s A", $currentUnixTime); ?></li>

</ul>


Hope this helps some...

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

Re: [gkornbluth] calculating date differences

By Deborah - June 5, 2011

Jerry,

Thank you for the date comparison recipes. I find those examples from the Cookbook very useful and have saved them for future use.

Meanwhile, I was able to expand upon an earlier post by Dave to arrive at a solution:
http://www.interactivetools.com/forum/gforum.cgi?post=72534;search_string=how%20many%20days;t=search_engine#72534

I decided to display just the number of years or months (if less than one year) since the hire date. The following code seems to be working:

<?php
$today = time() - strtotime($accountsRecord['hire_date']);
$yearsOld = intval($today/60/60/24/7/4.35/12);
$monthsOld = intval($today/60/60/24/7/4.35); ?>

<?php if ($yearsOld < 1):{echo $monthsOld;} ?> months
<?php elseif ($yearsOld == 1):{echo "1 year";} ?>
<?php else: ?><?php echo $yearsOld ?> years
<?php endif ?>

Not sure if this will account for leap year date hires, but I don't need to be that precise for this application.

Thanks again.
~ Deborah