Home arrow MySQL arrow Page 4 - Practical Date/Time examples with PHP and MySQL
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
TABLE OF CONTENTS:
  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
SEARCH DEVARTICLES

Practical Date/Time examples with PHP and MySQL - Dates with MySQL
(Page 4 of 6 )

MySQL has several great date and time functions that are very useful when you are working with dates. These functions are worth another article which I’ll be writing after this one.

Meanwhile I’ll show the ones that I think are the more useful and interesting.

Also, forcing MySQL to do the date operations by itself saves a lot of time, a lot of code and increases the performance of your application.

Personally, every time that I need to do some date calculations involving dates contained in a database I take a look at MySQL documentation to see which function can help me and let it do the job for me.

In all the following examples the dates can be taken from the database itself, your code or user input.

Anyway, if you are working with databases it’s obvious that at least one of the dates came from there.

Take care when using these functions as most of them work only on MySQL 3.22 and later. If you have doubts refer to the documentation.

The UNIX timestamp revisited

Programmers seem to like the UNIX timestamp very much because you can see it anywhere you go. In the case of MySQL you can use it like this:

SELECT UNIX_TIMESTAMP();

This will return the current timestamp.

mysql> SELECT UNIX_TIMESTAMP('1978-04-26 02:12:59');
+---------------------------------------+
| UNIX_TIMESTAMP('1978-04-26 02:12:59') |
+---------------------------------------+
|                             262422779 |
+---------------------------------------+

mysql> SELECT UNIX_TIMESTAMP('1978-04-26');
+------------------------------+
| UNIX_TIMESTAMP('1978-04-26') |
+------------------------------+
|                    262414800 |
+------------------------------+


This will print the timestamp of the given date.

Be careful when creating the structure and definitions for this kind of table. MySQL returns the UNIX timestamp as an unsigned integer, so keep this in mind when creating your database.

Now, imagine you have a database with a column that contains a UNIX timestamp, you can also convert it to a ‘human-readable’ date using MySQL like this:

mysql> SELECT FROM_UNIXTIME('262422779');
+----------------------------+
| FROM_UNIXTIME('262422779') |
+----------------------------+
| 1978-04-26 02:12:59        |
+----------------------------+



With this value it's just a matter of two lines of code and to use the function date_diff() that I created before:

// Working with mysql
$ndbconn = mysql_connect("localhost", "user", "password");
mysql_select_db("mydb");

// The query
$squery = "SELECT FROM_UNIXTIME('262422779'), NOW()";
$nresult = mysql_query($squery);
$s_given_date = mysql_result($nresult, 0, 0);
$s_curr_date = mysql_result($nresult, 0, 1);

// Free the result
mysql_free_result($nresult);

// Disconnected
mysql_close($ndbconn);

// The date difference
date_diff($s_given_date, $s_curr_date);


In this example I used MySQL to obtain the dates and then I calculated the time elapsed between both using my - now ‘famous’ - date_diff() function.

The UNIX timestamp can be any you have in your database, I selected the timestamp 262422779 that is exactly my ‘birth time’ and stored it in the $s_given_date variable.

Then I selected the current time with the NOW() function and stored it in the $s_curr_date variable. The rest is old news, I already explained how the function works.

blog comments powered by Disqus
MYSQL ARTICLES

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

Developer Shed Affiliates

 




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