DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

DateTime With PHP And MySql

  • submit to reddit
        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    


Snippets Manager replied on Sat, 2008/10/25 - 5:43pm

thank you man so muuch on the last 2 lines of code ! i've been looking for it for 3 days all over the net; it will enable me to implement timezone calculations for my radio's webpage . check it out and once again - THANK YOU!