subwayman's way is done through PHP, but you were referring to a database and you can do it all within SQL.
I'll assume the database you are referring to is MySQL. You didn't mention a version either. You see there's SQL functions to help do this easier if you have MySQL version 4.1.1 or higher. I'll assume you have a version less than 4.1 since most web host providers don't have the newer versions installed yet.
The MySQL example below retrieves four values just as an example. There's a TO_DAYS() SQL function available but it returns an integer as whole days. So, if the difference is less than a day, it will return zero as a value.
The UNIX_TIMESTAMP() SQL function returns seconds similar to the mktime() PHP function that subwayman is talking about. I used the ROUND() SQL function to return one digit after the decimal place since your post reflects that's what you want.
So, after you execute this query, you can check if "diff_in_whole_days" is zero, or you can use "diff_in_days" is less than 1.0, then you know that you should display the hours ("diff_in_hours") since you have less than a day difference between the two dates. Otherwise, you just display the number of days difference ("diff_in_days").
The "diff_in_seconds" is just an example of how to retrieve the seconds difference using SQL in case you want to do the calculations in PHP itself.
$sql = '
SELECT
TO_DAYS(NOW()) - TO_DAYS(date_column) AS diff_in_whole_days, # returns whole number (0-999)
ROUND((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(date_column)) / 86400, 1) AS diff_in_days, # days i.e. half a day = 0.5
ROUND((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(date_column)) / 60 / 60, 1) AS diff_in_hours, # hours i.e. 10.5
UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(date_column) AS diff_in_seconds # total seconds difference
FROM `table_name`';
// Do query here
To do it in PHP, first retrieve the date and time from your table using UNIX_TIMESTAMP(), then pass that along with the current date and time (using time() function) to the function below. It returns an array containing the difference between the two dates broken down by weeks, days, hours, minutes, and seconds. You can customize it to only return what you want.
// Pass epoch timestamps
function calcDateDiff ($date1 = 0, $date2 = 0) {
// $date1 needs to be greater than $date2.
// Otherwise you'll get negative results.
if ($date2 > $date1)
return FALSE;
$seconds = $date1 - $date2;
// Calculate each piece using simple subtraction
$weeks = floor($seconds / 604800);
$seconds -= $weeks * 604800;
$days = floor($seconds / 86400);
$seconds -= $days * 86400;
$hours = floor($seconds / 3600);
$seconds -= $hours * 3600;
$minutes = floor($seconds / 60);
$seconds -= $minutes * 60;
// Return an associative array of results
return array( "weeks" => $weeks, "days" => $days, "hours" => $hours, "minutes" => $minutes, "seconds" => $seconds);
}
// Example: current timestamp with 22:00 12th, January 2005 or use variable(s)
if ($diff = calcDateDiff(time(), mktime(22, 00, 00, 1, 12, 2005))) {
//Format output any way you like
printf("Difference between the two dates is: %d week(s), %d day(s), %d hour(s), %d minute(s), %d second(s).", $diff['weeks'], $diff['days'], $diff['hours'], $diff['minutes'], $diff['seconds']);
}
An example of the above codes output:
Difference between the two dates is: 38 week(s), 4 day(s), 20 hour(s), 34 minute(s), 10 second(s).
FYI - MySQL manual page on date and time functions:
http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
hth.
🙂
