Practical Date/Time examples with PHP and MySQL - Date Addition and Subtraction (
Page 4 of 5 )
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.
The code for this approach should look like this:
$ndbconn = mysql_connect("localhost", "user", "password");
mysql_select_db("mysql");
$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:
mysql> SELECT DATE_SUB('2004-03-01', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_SUB('2004-03-01', INTERVAL 1 DAY) |
+----------------------------------------+
| 2004-02-29 |
+----------------------------------------+
And don't worry about December 31st and January 1st transition:
mysql> SELECT DATE_SUB('2004-01-01', INTERVAL 1 DAY);
+----------------------------------------+
| DATE_SUB('2004-01-01', INTERVAL 1 DAY) |
+----------------------------------------+
| 2003-12-31 |
+----------------------------------------+
/ 74 
