Getting a Date with PHP & MySQL

Recently I was working on an event management project in PHP & MySQL which made me realize that there must be a good solution for interacting with dates and times;  a solution that doesn’t rely on PHP’s date format being stored as character data in MySQL, and doesn’t require the parsing out of MySQL’s DATETIME format in PHP.  Both PHP and MySQL have built-in functions for dealing with dates; it seems silly to have to give up one set of functionality for the other.

But it turns out one doesn’t have to!  Both PHP and MySQL share a common understanding of the Unix Timestamp.  One can convert a Unix timestamp into MySQL DATETIME and also pass it to the PHP date() function which provides a great deal of flexibility.

The idea is pretty simple; use MySQL’s built-in FROM_UNIXTIME() function to store a timestamp in the DATETIME format and then, when retrieving a date, use the UNIX_TIMESTAMP() function to convert the DATETIME back into a timestamp that can be passed as a parameter to php’s date() function.

Here are some examples.

Creating a timestamp and then inserting into a table named ‘events’ in the field named ‘date’ of type DATETIME
[cc lang=”php”]

$dateTime = mkTime($hours, $minutes, $seconds, $month, $day, $year);
mysql_query(“INSERT INTO events (date) VALUES(FROM_UNIXTIME($dateTime))”);

Retrieving a timestamp from the same database, and then formatting it with PHP’s date() function
[cc lang=”php”]
$res = mysql_query(“SELECT UNIX_TIMESTAMP(date) FROM events”) ;
$row = mysql_fetch_row($res);
$dateStr = date(“M j, Y g:i a”, ($row[0]));

This code is certainly not complete.  It’s just intended to provide a quick idea of how this works.  For more information on the PHP date function check out the PHP documentation

This entry was posted in Article, PHP. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

Post a Comment

Your email is never published nor shared. Required fields are marked *

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>