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

Practical Date/Time examples with PHP and 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.

Author Info:
By: Mauricio Cuenca
Rating: 4 stars4 stars4 stars4 stars4 stars / 44
January 12, 2003
  1. · Practical Date/Time examples with PHP and MySQL
  2. · The UNIX timestamp
  3. · How old are you? (exactly)
  4. · Dates with MySQL
  5. · Date Addition and Subtraction
  6. · Conclusion

print this article

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.

The code for this approach should look like this:


$ndbconn = mysql_connect("localhost", "user", "password");

$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

// Disconnected
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                             |

blog comments powered by Disqus

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 – More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials