Over a million developers have joined DZone.

DateTime With PHP And MySql

DZone's Guide to

DateTime With PHP And MySql

Free Resource
This script shows, by example, how to convert from a UNIX Timestamp (the returned data type from the PHP time() or mktime() functions) into a string that can be stored in MySql's DATETIME or TIMESTAMP fields. It also shows how to convert back into a UNIX Timestamp. The times are stored in the database as GMT/UTC times, so finally, it shows how to convert from the resulting UNIX timestamp into a string that represents the time with a given GMT/UTC offset.

Create the UNIX Timestamp, using the current system time.

// Create the UNIX Timestamp, using the current system time
$tUnixTime = time();

Convert that UNIX Timestamp into a string, safe for MySql. The string will be in the format CCYY-MM-DD HH:MM:SS and will represent the GMT/UTC time represented by the UNIX Timestamp. Example: 2006-02-10 20:33:55

// Convert that UNIX Timestamp into a string (GMT), safe for MySql
$sGMTMySqlString = gmdate("Y-m-d H:i:s", $tUnixTime);

The string created above is suitable for sending to MySql and storing in a DATETIME or TIMESTAMP field. This snippet does not include the code that would store and retrieve the data.

Parse the String and store the result in a new UNIX Timestamp. Because strtotime() thinks that any string passed to it, that does not specify a timezone, is in the local timezone, according to the settings in your configuration, we append " GMT" to the string that is returned from MySql. This creates a new string that is both compliant with strtotime() and explicitly a GMT value.

// Parse the String into a new UNIX Timestamp
$tParsedTime = strtotime($sGMTMySqlString . " GMT");

Show the two values, in W3C format, for debugging. W3C format dates include the full date, time and timezone information. If we visually or programmatically compare the two values, they should be identical in all aspects.

// Show the Original Time and Parsed Time on the screen, in W3C Format
print "Original Time: " . date(DATE_W3C, $tUnixTime);
print "Parsed Time: " . date(DATE_W3C, $tParsedTime);

The following two lines demonstrate how to take one of these UNIX timestamps and convert it into a user-friendly string, using a time offset. Typically, you would store this time offset in your user's profile.

// Create a String showing the DateTime in CCYY-MM-DD Format in a Specified Zone
$fUTCOffset = +2.00; // GMT/UTC Offset in Hours (2.50 = 2 hours 30 minutes)
print "Time in GMT+2: " . gmdate("Y-m-d H:i:s", $tUnixTime + $fUTCOffset * 3600);

I am not sure whether my idea to use a floating point number to represent the user's time zone is correct. I must check the standard. If the standard is to use a time-span value (2.50 is not 2 hours 30 minutes but 2 hours and 50 minutes, 2.30 is 2 hours and 30 minutes) I will change it. This is a minor change.

I did not know of a PHP function that would convert a GMT/UTC UNIX Timestamp into a string representing a time in a specified zone. All I could find is the date() function which uses the current zone according to the config and the gmdate() zone which returns GMT/UTC. To implement the functionality, I modified the date by adding to it and then converting to GMT/UTC. I do not like this, but it works.

This code makes no attempt to guess whether the current user is affected by DST or not. It simply uses an offset from GMT/UTC. According to my research, DST guessing is dodgy at best and, according to some, soon to be outdated by a change to the DST system in America.

I am not sure, even if I knew how, that I would trust built-in time zone functionality. I would suspect its accuracy for the same reasons I don't use automatic guessing of DST status.

Version 0.0.1 - 2006-02-10
Stephen's Modules: DateTime with PHP and MySql
This work is licensed under a Creative Commons Attribution-ShareAlike 2.5 License

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}