Calculates the difference between two dates in PHP

Question

HI, I have several messages on my MySql database server, one of the information materials in each message is the date and time in datetime format (example 2010-11-26 21: 55: 09) when the message was made.

So, I want to get the actual date and time from the SQL server using the NOW () function and counts how many seconds or minutes or hours or days ago a message was posted.

I don't know how to create this php script, but I know what has probably already been done, so thanks for any help.

Answer 1

As billythekid said, you can use the date_diff() function, if you are using PHP5.3 +, if you are not, then there are various methods. As other posters showed. The fastest method in MySQL, if you want to know the time divided by the "hours: mins: secs" hierarchy, is to use the TIMEDIFF () function.

 SELECT TIMEDIFF(NOW(), '2010-11-26 12:00:00'); 

If you want to use it as seconds, use the timestamp unix functions in MySQL or PHP, you can quickly convert MySQL dates to PHP using strtotime() .

Answer 2

you can use date_diff () function

http://php.net/manual/en/function.date-diff.php

Something like...

 <?php $now = time(); $then = $posttime; $diff = date_diff($now,$then); echo $diff->format('%R%d days'); #change format for different timescales ?> 

edit -

I really solve this problem in one of my Twitter apps using this feature ...

 function time_since ( $start ) { $end = time(); $diff = $end - $start; $days = floor ( $diff/86400 ); //calculate the days $diff = $diff - ($days*86400); // subtract the days $hours = floor ( $diff/3600 ); // calculate the hours $diff = $diff - ($hours*3600); // subtract the hours $mins = floor ( $diff/60 ); // calculate the minutes $diff = $diff - ($mins*60); // subtract the mins $secs = $diff; // what left is the seconds; if ($secs!=0) { $secs .= " seconds"; if ($secs=="1 seconds") $secs = "1 second"; } else $secs = ''; if ($mins!=0) { $mins .= " mins "; if ($mins=="1 mins ") $mins = "1 min "; $secs = ''; } else $mins = ''; if ($hours!=0) { $hours .= " hours "; if ($hours=="1 hours ") $hours = "1 hour "; $secs = ''; } else $hours = ''; if ($days!=0) { $days .= " days "; if ($days=="1 days ") $days = "1 day "; $mins = ''; $secs = ''; if ($days == "-1 days ") { $days = $hours = $mins = ''; $secs = "less than 10 seconds"; } } else $days = ''; return "$days $hours $mins $secs ago"; } 

You pass it to the unix timestamp to check (publish time), and it returns a different string.

Answer 3

Usually you do similar things in a query, but MySQL is not very good at intervals (this would be very easy with PostgreSQL). You can convert it to a unix timestamp, then it will give the number of seconds between two dates:

 SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(your_datetime_column); 

I was thinking about DATEDIFF, but it returns the number of days between two dates.

You can do this in PHP, for example, using the DateTime class:

 $date1 = new DateTime(); $date2 = new Datetime('2010-11-26 12:00:00'); var_dump($date1->diff($date2)); 

(There's a procedural way to do this if you are not a fan of OOP.)
This is definitely a solution that I would use if I cannot do it using RDBMS. DateTime :: diff returns a DateInterval object that contains the number of seconds, minutes, hours, days, etc. Between two dates.

You can also do this using timestamps in PHP:

 $num_sec = time() - strtotime('2010-11-26 12:00:00'); 

To return the same thing as the SQL query.

Answer 4

Inside SQL Query, a simple solution is possible:

 SELECT UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(post_date) AS seconds_ago FROM posts 

Documentation here: MySQL Ref