In this article Mauricio shows us some examples of how to use the date/time features with MySQL and PHP including the UNIX timestamp and the PHP date_diff() function.
Practical Date/Time examples with PHP and MySQL - Date Addition and Subtraction (Page 5 of 6 )
Suppose that you have a members only area, each user has a password but you want the user to change his or her password every two weeks.
The first thing you have to do is add a new column to your users table; this column will store the timestamp of the exact date when the user changed his or her password.
Remember, again, that the timestamp is an unsigned integer, so keep this in mind when creating the table.
$n_start_date = 1062521254; // Timestamp for 2003-09-02 11:47:34 taken from your DB
// The query adds fourteen days to the given date $squery = "SELECT DATE_ADD(FROM_UNIXTIME($n_start_date), INTERVAL 14 DAY)"; $nresult = mysql_query($squery); $s_new_date = mysql_result($nresult, 0, 0);
// Free the result mysql_free_result($nresult);
// Disconnected mysql_close($ndbconn); echo "Your password will expire on $s_new_date";
?>
The main function here is DATE_ADD(), this MySQL function perfectly adds any number of seconds, minutes, hours, days or years to a specific date.
In this example we used it to calculate the exact time fourteen days after the selected date. You can use this function with no risk, it even takes care of leap years. For example:
mysql> SELECT DATE_ADD('2004-02-28 23:50:00', INTERVAL 10 MINUTE); #2004 is a leap year +-----------------------------------------------------+ | DATE_ADD('2004-02-28 23:50:00', INTERVAL 10 MINUTE) | +-----------------------------------------------------+ | 2004-02-29 00:00:00 | +-----------------------------------------------------+
Subtraction is done with the DATE_SUB() function in the same way: