Date Addition and Subtraction&toc - MySQL

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 |
+----------------------------------------+

>>> More MySQL Articles          >>> More By Mauricio Cuenca

blog comments powered by Disqus