Managing differences in date formats between PHP and MySQL

Question

I am writing my first PHP application, which should deal directly with dates, and thus deal directly with the fact that PHP and MySQL have different date formats.

My question is: what is the most elegant way to handle this difference?

I have the following two functions for managing difference using php:

function mysql_date($php_date) { return date( 'Ymd H:i:s', $php_date ); } function php_date($mysql_date) { $val = explode(" ",$mysql_date); $date = explode("-",$val[0]); $time = explode(":",$val[1]); return mktime($time[0],$time[1],$time[2],$date[1],$date[2],$date[0]); } 

is there an easier way to manage this directly in my SQL queries?

Or could you suggest any other more elegant way to handle this?

Answer 1

Since (around) PHP 5.2, PHP has a built-in class / object for working with the Dates and Times called DateTime . In the void, it is always better to use the built-in than to argue with dirty parts.

The DateTime constructor (or the date_create function) accepts a date in any format that strToTime understands. All you need to know about strToTime is magical voodoo that will correctly recognize the date in almost any string format. When I first encountered strToTime, I had the same internal reaction as now ("this garbage / seems unreliable"). Not this. It just works so that your own fragile understanding of dates will never happen (and if you think you understand dates, you don’t do this. Trust me.)

So, pull the information from MySQL as a Date / Time string and instantly create the PHP Date Object. Use the date_format method (with some convenient constants ) when / if you need the date again as a string.

Answer 2

You can replace php_date with strtotime .

 $php = strtotime($mysql); 

MySQL equivalent would be UNIX_TIMESTAMP .

Although, if you want to handle formatting in SQL, try MySQL DATE_FORMAT .

Answer 3

Store everything in the database in the date and time field in UTC. To manipulate PHP, use PEAR Date . I'm not a big PEAR user, but this library is fantastic and will handle all the annoying date conversion issues that you shouldn't waste your time worrying about.

Answer 4

I would recommend you store everything in mysql format until you need to display it for the user, then use strtotime () to get unix timestamp and date () to format it.

If you introduce the Hungarian notation, it's even harder to go wrong:

 $ymdDateAdded = date('Ym-d'); $timeDateAdded = strtotime($ymdDateAdded); $userDateadded = date('j F Y', $timeDateAdded); 

Answer 5

I think it would be better idea to store unix timestamps in the DB field. When you need to display dates for the human language, you can always use the php date () function. For everything else, just use a digital timestamp.

Answer 6

You can create a small date object that simply converts the date as needed.

 $Date_p = new MagicDate($php_date); $Date_m = new MagicDate($mysql_date); 

$Date_p and $Date_m just show that you can sow the object anyway you need. If you need mysql date, you will have code. Actually, this would be something pretty general, like $Date .

 $query = "UPDATE SET Date='".$Date_p->toMysql()."' "... 

and vice versa, when you need the opposite. You can use already created functions. Just add a sniffer to the build method, for example:

 public function __construct($date) { $phpdate = strtotime($date); if($phpdate) { $this->phpdate = $phpdate; $this->mysqldate = $this->mysql_date($phpdate); } else { $this->phpdate = $this->php_date($phpdate); $this->mysqldate = $phpdate; } } 

Throw some processing errors to catch things that go wrong. Add recipient for two dates. And this is a set and forget about it. Just pull out the correct date when you need it.

There may be some optimizations, but this will just show you how this might work.

Answer 7

It is best to save time and date as a unix timestamp rather than other formats.

I created a class to handle date and time in php. Its easy to use and very useful.

 <?php define("NEW_LINE", "</BR>"); class scTimestamp { private $timestamp; private $year; private $month; private $day; private $hour; private $minute; private $second; public function __construct() { register_shutdown_function(array($this,'__destruct')); $this->setTimestamp($_SERVER['REQUEST_TIME']); } public function __destruct() { unset($this->timestamp); unset($this->year); unset($this->month); unset($this->day); unset($this->hour); unset($this->minute); unset($this->second); } private function rebuildTimestampFromDate() { $this->timestamp=mktime($this->hour,$this->minute,$this->second,$this->month,$this->day,$this->year); } private function rebuildDateFromTimestamp() { $this->day=date('j',$this->timestamp); $this->month=date('n',$this->timestamp); $this->year=date('Y',$this->timestamp); $this->hour=date('g',$this->timestamp); $this->minute=date('G',$this->timestamp); $this->second=date('s',$this->timestamp); } public function setTimestamp($tempTimestamp) { $this->timestamp=$tempTimestamp; $this->rebuildDateFromTimestamp(); } public function getTimestamp() { return $this->timestamp; } public function setYear($tempYear) { $this->year = $tempYear; $this->rebuildTimestampFromDate(); } public function getYear() { return $this->year; } public function setMonth($tempMonth) { $this->month = $tempMonth; $this->rebuildTimestampFromDate(); } public function getMonth() { return $this->month; } public function setDay($tempDay) { $this->day=$tempDay; $this->rebuildTimestampFromDate(); } public function getDay() { return $this->day; } public function setHour($tempHour) { $this->hour = $tempHour; $this->rebuildTimestampFromDate(); } public function getHour() { return $this->hour; } public function setMinute($tempMinute) { $this->minute = $tempMinute; $this->rebuildTimestampFromDate(); } public function getMinute() { return $this->minute; } public function setSecond($tempSecond) { $this->second = $tempSecond; $this->rebuildTimestampFromDate(); } public function getSecond() { return $this->second; } public function getDateDifferenceFromNow() { return $this->getDateDifferenceFrom($_SERVER['REQUEST_TIME']); } public function getDateDifferenceFrom($fromDate) { $return=""; $sec=" Second"; $min=" Minute"; $hrs=" Hour"; $before=" Before"; $difference=$fromDate-$this->getTimestamp(); if($difference<0) $return.="In the Future"; else if($difference<60) { if($difference>1) $sec.="s"; $return.= $difference.$sec.$before; } else if($difference<3600) { $difference=intval($difference/60); if($difference>1) $min.="s"; $return.=$difference.$min.$before; } else if($difference<86400) { $difference=intval($difference/3600); if($difference>1) $hrs.="s"; $return= $difference.$hrs.$before; } else if($difference<604800) { $return.= date("lg:ia",$this->getTimestamp()); } else if($difference<28512000) { $return.= date("F j",$this->getTimestamp()); } else { $return.= date("F j, Y, g:ia",$this->getTimestamp()); } return $return; } public function getDateAsString() { return date("F j, Y",$this->getTimestamp()); } public function getDateTimeAsString() { return date("F j, Y, g:ia",$this->getTimestamp()); } public function __toString() { $return = NEW_LINE."^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^"; $return.= NEW_LINE." ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^".NEW_LINE; $return.= NEW_LINE."@@ Timestamp: ".$this->getTimestamp()." @@".NEW_LINE; $return.= NEW_LINE."@@ Date: ".$this->getDateTimeAsString()." @@".NEW_LINE; $return.= NEW_LINE." ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^".NEW_LINE; return $return; } } ?> 

after its inclusion it can be used as follows

 include_once("scTimestamp.php"); $test=new scTimestamp(); echo $test->getDateAsString(); $test->setTimestamp(1210203200); echo $test->getDateDifferenceFromNow(); echo $test; $test->setTimestamp(121020320022); echo $test->getYear(); echo $test; 

And the result will be pleasant.

June 26, 2015 May 7, 2008, 23:33 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^ @@ Timestamp: 1210203200 @@ @@ Date: May 7, 2008, 11:33 pm @@ ^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 5804 ^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ @@ Time mark: 121020320022 @@ @ @ Date: December 25, 5804, 3:33 @@ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^

This class can be used as needed.