Home arrow MySQL arrow Page 4 - Practical Date/Time examples with PHP and MySQL

Date Addition and Subtraction&toc - MySQL

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.

TABLE OF CONTENTS:
  1. Practical Date/Time examples with PHP and MySQL
  2. The UNIX timestamp
  3. How old are you? (exactly)
  4. Date Addition and Subtraction
  5. Conclusion
By: Mauricio Cuenca
Rating: starstarstarstarstar / 98
April 22, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: