By using this site you agree to the use of cookies by Brugbart and our partners.

Learn more

MySQL Datetime Conversion

Should you use strtotime or regular expressions for conversion?

Edited: 2012-06-25 20:33

Maybe you have just made the move from unix timestamps to MySQL datetime stamps, or you may be considering it due to the 32 bit limit of unix time stamps, which limits the time stamp at around year 2038. There are different limits that apply to the datetime stamp, so depending on your requirements, you may not want to move to using that instead.

MySQL's official site lists datetime as having a limit of 000-01-01 00:00:00 to 9999-12-31 23:59:59 – this should be enough for most applications these days, and if it finally becomes relevant some day, the limits are likely to have changed.

If you have chosen to use the datetime format of MySQL, then you might also be thinking about how you should convert this format to match your preferred local time format.

One way to do this would be to first convert it to unix time using PHP's strtotime and then convert it back to a readable format – but this will likely just defeat the purpose of using the datetime format.

Same 32 bit limits still apply for strtotime, which would make your dates show up as something like 1970-01-01 – a default output when something goes wrong – but they would still show correctly in your database, so its more of a visual problem.

There are ways to overcome these limits, and still benefit from using the datetime format – such as moving to a 64bit server – but this Article is about how you convert these dates, without the use of strtotime.

MySQL Datetime Conversion

Simple conversion can be done with a regular expression, using the preg_match function of PHP, which will make each part easily accessible in an array. An example of this is shown below:

<?php
$str = '2012-07-08 21:23:09';

preg_match('/([0-9]{4})-([0-9]{2})-([0-9]{2})\s{1}([0-9]{2}):([0-9]{2}):([0-9]{2})/', $str, $ExpressTime);

echo $ExpressTime[1]." Year\n"; // Year
echo $ExpressTime[2]." Month\n"; // Month
echo $ExpressTime[3]." Day\n"; // Day

echo $ExpressTime[4]." Hour\n"; // Hour
echo $ExpressTime[5]." Minute\n"; // Minute
echo $ExpressTime[6]." Second\n"; // Second

header('Content-type: text/plain'); // Used for output formatting, optional.
?>

The above would allow you to re-order the datetime format anyway you like.

The regular expression used is very simple, the content of each parentheses is remembered in its own array element. The [0-9]{4} part will only match numbers – [0-9] – which can be a maximum of four – {4} – characters in length.

You can also create a custom function, to return an array containing the date and time. I.e.

<?php
 function ExpressTime($input)
 preg_match('/([0-9]{4})-([0-9]{2})-([0-9]{2})\s{1}([0-9]{2}):([0-9]{2}):([0-9]{2})/', $input, $ExpressTime);
 return $ExpressTime;
}

$str = '2012-07-08 21:23:09';

$Expressed = ExpressTime($str); // Do this when you want a date/time expressed

echo $Expressed[1]." Year\n"; // Year
echo $Expressed[2]." Month\n"; // Month
echo $Expressed[3]." Day\n"; // Day

echo $Expressed[4]." Hour\n"; // Hour
echo $Expressed[5]." Minute\n"; // Minute
echo $Expressed[6]." Second\n"; // Second

header('Content-type: text/plain'); // Used for output formatting, optional.
?>

Using Strtotime in Conversion

If you still want to use strtotime, even after the disadvantages has been highlighted, then you can use the below code.

echo date("d/m/Y G:i", strtotime($DB_Result['TimeStamp']));