[SOLVED] Calculate years and months of service

[Eiolon]

I have each employees hire date stored in date format in MySQL (YYYY-MM-DD) and would like to calculate how many years and months of service they have.  The output would be like:

 

Years of Service: 7 yr and 2 mo

 

How would I go about doing this calculation?

 

Thanks!

[Lumio]

Use an INT-type... if you use DATE-type...:

<?php
   $started = strtotime($youDate);
   $now = time();
   $dif = $now-$started;
   echo floor($dif/(60*60*24*365))." years ".floor($dif/(60*60*24*30))." months";
?>

[Eiolon]

Hmmm... this is the result I am getting:

 

37 years 461 months

 

The date is 1998-09-01.

 

 

<?php
   $started = strtotime($row_employee['hire_date']);
   $now = time();
   $dif = $now-$started;
   echo floor($dif/(60*60*24*365))." years ".floor($dif/(60*60*24*30))." months";
?>

[Barand]

or this which doesn't approximate every month to 30 days

<?php
$hiredate = '1971-08-16';
$htime = strtotime($hiredate);

$years = date('Y') - date('Y', $htime);

if (date('m') < date('m', $htime)) {
    $years--;
    $months = date('n') + 12 - date('n', $htime);
}
elseif (date('m') == date('m', $htime))
{
    if (date('d') < date('d', $htime))
    {
        $years--;
        $months = 11;    
    }
    else $months = 0;
}
else $months = date('n') - date('n', $htime);

echo "$years Years, $months months";
?>

[Psycho]

This code uses the date function to accurately determine years & months

 

<?php

  $startDate = strtotime($date);
  $endDate = time();
  $dif = $endDate - $startDate;

  $years = date('Y', $dif) - 1970;
  $months = date('n', $dif) - 1;

  echo $years." years ".$months." months<br><br>";

?>

 

EDIT: To explain this a little. Once you get the difference between today and the start date, you have the difference in the number of seconds between the two dates. A timestamp is the number of seconds since Jan 1, 1970. So, just use the difference as a timestamp to determine the number of years and months since Jan 1, 1970.

[obsidian]

Keep in mind that all the solutions using the PHP date and time functions will only work for people who have begun employment since January 1, 1970, since that's when the UNIX epoch was, and PHP cannot calculate times before that. If you think you will ever have employees with more than those 37 years of service, you may wish to allow MySQL to do your calculations for you:

SELECT DATEDIFF(hiredate, CURDATE()) AS emp_span FROM table;

 

Then, you can use your emp_span (returned in number of days they have been employed) to calculate the exact number of months/days using one of the methods mentioned above.

[Eiolon]

EDIT: NVM solved.  Thanks to all!

[kenrbnsn]

Keep in mind that all the solutions using the PHP date and time functions will only work for people who have begun employment since January 1, 1970, since that's when the UNIX epoch was, and PHP cannot calculate times before that.

This is necessarily true anymore.  As of v5.1.0:

The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that correspond to the minimum and maximum values for a 32-bit signed integer). However, before PHP 5.1.0 this range was limited from 01-01-1970 to 19-01-2038 on some systems (e.g. Windows).

 

Ken

[Psycho]

Keep in mind that all the solutions using the PHP date and time functions will only work for people who have begun employment since January 1, 1970, since that's when the UNIX epoch was, and PHP cannot calculate times before that.

 

That is not true. From the manual:

The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that correspond to the minimum and maximum values for a 32-bit signed integer). However, before PHP 5.1.0 this range was limited from 01-01-1970 to 19-01-2038 on some systems (e.g. Windows).

 

The code I provided uses the difference as a timestamp. So the difference in dates cannot exceed approx 68 years because 1970 + 68 = 2038. I have tested and validated this. So unless there is a possibility that employees will be working for you for more than 67+ years it will not produce any errors.

[obsidian]

That is not true. From the manual:

The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that correspond to the minimum and maximum values for a 32-bit signed integer). However, before PHP 5.1.0 this range was limited from 01-01-1970 to 19-01-2038 on some systems (e.g. Windows).

 

The code I provided uses the difference as a timestamp. So the difference in dates cannot exceed approx 68 years because 1970 + 68 = 2038. I have tested and validated this. So unless there is a possibility that employees will be working for you for more than 67+ years it will not produce any errors.

 

Yes, it is true, and if you'll post the rest of the quote you cite from the manual, you will see that kenrbnsn is right with the fact that if you're running PHP5, it isn't a problem any more. Sadly, though, there are a lot of hosts out there still not running PHP5, so you need to be aware of the limitations and be sure you know what it is that your host provides. In addition, if we're talking about the difference in timestamps, you're still having to be aware of the difference between Jan 1, 1970 and the present date, so, as i said in my previous post, as of right now, you would be limited by 38 years of service, since we are nearly to Jan 1, 2008.

 

Now, if you have any doubt about the limitations of UNIX time, read up on it a bit here, and realize that until PHP5.1, these limitations were applied to any PHP date and time functions.

[Psycho]

In addition, if we're talking about the difference in timestamps, you're still having to be aware of the difference between Jan 1, 1970 and the present date, so, as i said in my previous post, as of right now, you would be limited by 38 years of service, since we are nearly to Jan 1, 2008.

 

Again - not true! The current year has no relevance when computing the difference between two years. The code I provided will work correctly even on a PHP4 server as long as the two dates you are comparing are within the allowed dates and the difference is not more than 67 years.

 

Let's say you generate the difference between timestamps for 1-1-1950 (-631130400) and 1-1-2000 (946706400) - a 50 year difference. The result is 1577836800 seconds. And if you use that as a timestamp the result is "12-31-2019 06:12:00" (there is a discrepency of 18 hours I didn't notice before, but it can be accomodated in the code.). Now you can compute the difference in years/months by computing how man years/months that date is from 1-1-1970. Therefore the max difference is ~67-68 years.

 

I have tested and validated this. If you think this is incorrect I would welcome you to provide any two dates that are between 1902 to 2038 which are no more than 67 years apart where you believe the code will fail.

[obsidian]

I have tested and validated this. If you think this is incorrect I would welcome you to provide any two dates that are between 1902 to 2038 which are no more than 67 years apart where you believe the code will fail.

 

Man, somebody has a chip on his shoulder... I never once attacked your code at all. I was simply referring to the limitations of the functions themselves.

[Psycho]

No, no chip. But your statements have been incorrect.

 

if we're talking about the difference in timestamps, you're still having to be aware of the difference between Jan 1, 1970 and the present date, so, as i said in my previous post, as of right now, you would be limited by 38 years of service, since we are nearly to Jan 1, 2008.

 

What does the fact that we are in 2008 have to do with computing the difference between two different timestamps? Assuming you are using PHP4, you can theoretically compute the difference up to 136 years (2038-1902). But considering we are in 2007 and this is for years of service; as long as the person did not start working prior to 1902 you can use a timestamp to compute the difference.

[obsidian]

No, no chip. But your statements have been incorrect.

 

if we're talking about the difference in timestamps, you're still having to be aware of the difference between Jan 1, 1970 and the present date, so, as i said in my previous post, as of right now, you would be limited by 38 years of service, since we are nearly to Jan 1, 2008.

 

What does the fact that we are in 2008 have to do with computing the difference between two different timestamps? Assuming you are using PHP4, you can theoretically compute the difference up to 136 years (2038-1902). But considering we are in 2007 and this is for years of service; as long as the person did not start working prior to 1902 you can use a timestamp to compute the difference.

 

Because, typically speaking, the difference between two dates that one would be calculating is from the current time. I have done enough work with date/time functions to know what I'm about in this category. This isn't the first time I've solved a date/time issue. When you ask someone how long they have been working at a company, it's obviously implying that the calculation be done from the time RIGHT NOW back to the time they started. Therefore, knowing that the UNIX timestamp limitation only allows for dates and times back to 1970, anyone who has started work before that time cannot be calculated using strictly the date/time functions. Again, for the second time I've posted this correction to something faulty you've said:

Re PHP 5.1 - The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that correspond to the minimum and maximum values for a 32-bit signed integer). However, before PHP 5.1.0 this range was limited from 01-01-1970 to 19-01-2038 on some systems (e.g. Windows).

 

As is stated right there, and re-enforced with a simple Google search for "UNIX timestamp limitations," we can easily see that PHP4 is limited by the 1970-01-01 start time, not 1902 like you have stated. I have already admitted that my statement was inaccurate when dealing with PHP5.1+, but not with PHP4. So, thinking things through logically, to find out how long someone has been with a company (current employee), I simply need a starting date, and it's easy to see where the limitation will cause problems:

<?php
$start_date = '1958-01-01';
if (($ts = strtotime($start_date)) !== FALSE) // Here is where the change will die for dates before 1970-01-01 because of UNIX timestamp (same as date function)
{
  $diff = time() - $ts;
  // Calculate your number of years here
}
?>

[Psycho]

Well, iterestingly enough I  am able to use strtotime() to create valid negative timestamps on both a Windows and Linux server (using PHP4). And they are properly interpreted by the date() funtion as well.