Zend_Date and MySQL’s datetime format

Despite Zend_Date‘s power and elegance to handle all kinds of different date and time formats, it (still) lacks the ability to cope with MySQL’s datetime formated strings out-of-the-box.

Thus, instead of being able to simply do something like

$date = new Zend_Date();
echo $date->toString(Zend_Date::MYSQL_DATETIME);

one has to manually define MySQL’s datetime syntax, as shown in the following:

$date = new Zend_Date();
echo $date->toString('YYYY-MM-dd HH:mm:ss');

I personally don’t quite see why this commonly used format has not yet been added to Zend_Date.

In the meantime, I have found it to be the most convenient to define the MySQL datetime format string as a constant. Although this is no big deal but still, I would rather like to see this simple piece of formating code inside Zend_Date.

UPDATE
I have come across a strange problem when trying to create new Zend_Date objects using custom formats, such as the MySQL datetime format shown above, e.g.:

$date = new Zend_Date($mysqlDatetimeString, MYSQL_DATETIME); //will create a wrong date...

Instead it seems best to create custom format dates/times using a two way approach:

$date = new Zend_Date(); //create new object, by default time()
$date->set($mysqlDatetimeString, MYSQL_DATETIME); //will create correct date

Comments

5 responses to “Zend_Date and MySQL’s datetime format”

  1. Joe C Avatar
    Joe C

    This was very useful, thanks 🙂

    1. matthias.kerstner Avatar

      Hello Joe,

      thanks for your feedback. I have updated the entry to mention a problem when creating Zend_Date objects based on custom formats.

      Best regards 🙂

  2. Scott Benton Avatar
    Scott Benton

    Thanks a ton for the info! I spent hours trying to figure out why some of my dates were coming out wrong. The worst part is it is not consistent! When creating Zend_Date using the first above, it will work fine with some dates, but not others. Very strange that it isn’t consistent. Thanks again.

  3. Marco Avatar
    Marco

    Hi!
    http://framework.zend.com/manual/1.12/en/zend.date.creation.html#zend.date.creation.database Example #4
    says that mysql datetime fields also work with the iso8601 format, which is supported by Zend_Date::ISO_8601. To create a Zend_Date from a mysql datetime string you don’t even need that.

    To create a Zend_Date from a mysql datetime string:
    $date = new Zend_Date(‘2014-03-18 16:07:00’);
    echo $date->toString(); // output ‘18.03.2014 16:07:00’

    To use a Zend_Date in a mysql query:
    $date = new Zend_Date(‘2014-01-01’);
    $select = $someTable->select(); // $someTable may be an object of a class that inherits from Zend_Db_Table_Abstract
    $select->where(‘my_date_column >= ?’, $date->toString(Zend_Date::ISO_8601));
    echo $select->__toString() //output maybe something like ‘SELECT `sometable`.* FROM `somedb`.`sometable` WHERE (my_date_column >= ‘2014-01-01T00:00:00+01:00′)’, which still will work for a mysql datetime

    Best regards
    Marco

  4. Marijn Avatar
    Marijn

    What about this one-line solution?

    $mySqlDateNow= substr(Zend_Date::now()->get(Zend_Date::ISO_8601), 0, 10);

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.