CODEWITHSUNDEEP
magentodatetime
Morfie
Morfie

Reputation: 670

Magento displaying date from MySQL incorrectly

In Magento, I have a phtml that iterates over a collection of a custom model which inherits from Mage_Core_Model_Abstract. This model has a DB field called 'date'.

When the phtml displays the output of the getDate() function to the browser for each item it is iterating over, most of the time it displays the correct date. For ambigous dates (date that can be interpreted M/d/Y or d/M/Y, it does it wrong. I checked date field (datetime) in the MySQL table, and all the dates are stored correctly. Something in PHP is interpretting the dates wrong once it reads it in from the DB.

For example:

In MySQL            => HTML Output => status
2013-12-05 00:00:00 => 5/12/2013   => Wrong
2013-11-30 00:00:00 => 11/30/2013  => Correct
2013-11-06 00:00:00 => 6/11/2013   => Wrong
2013-10-11 00:00:00 => 11/10/2013  => Wrong
etc.

I've done google searching and looking in StackOverflow, but I'm not sure how to fix this issue. Any ideas? Thanks.

Update: I used printLogQuery(true) on the collection to get the query it was executing. It was as simple as: SELECT main_table.* FROM physicians_event AS main_table;

To give more background on how the date is going from the DB to HTML: In a phtml, I iterate over a custom collection:

<?php foreach ($this->getEventCollection() as $_event): ?>
    <?php echo $this->getLayout()->createBlock('physicians/event')
                    ->setEvent($_event)
                    ->setTemplate('physicians/event/row.phtml')
                    ->toHtml() ?>
<?php endforeach ?>

getEventCollection looks like this:

public function getEventCollection() {
    if ($this->getSortBy() === null) {
        $this->setSortBy('date');
    }

    if ($this->getLimit() === null) {   
        $this->setLimit(10);
    }

    $collection = Mage::getModel('physicians/event')->getCollection();
    $collection->setOrder($this->getSortBy())
               ->setPageSize($this->getLimit());

    if ($this->getEventType() !== null) {
        $collection->addFieldToFilter('type', array('eq' => $this->getEventType()));
    }

    return $collection;
}

When everything is output as HTML (row.phtml that is rendered for each item in the collection), the date is output like this:

<?php echo $this->getEvent()->getFormattedDate('M/d/Y') ?>

The getFormattedDate function looks like this:

public function getFormattedDate($format = null, $puredate=false) {
    $date = new Zend_Date($this->getDate());
    if ($format === null) {
        $format = 'MMMM d, Y';
    }
    return (!$puredate)?$date->toString($format):$date;
}

I believe that is the full story so far. Ideas? Thank you again!

Update: further debugging. Inside of getFormattedDate, I added output statements to trace where the data goes wrong:

echo "Collection Item Date: " . var_export($this->getDate()) . "<br/>\n";
$date = new Zend_Date($this->getDate());
echo "Zend Item Date: " . var_export($date,true) . "<br/>\n";

The output looked like this:

Collection Item Date: '2013-07-06 00:00:00'

Zend Item Date: Zend_Date::__set_state(array(
   '_locale' => 'en_US',
   '_fractional' => 0,
   '_precision' => 3,
   '_unixTimestamp' => '1370563200',
   '_timezone' => 'UTC',
   '_offset' => 0,
   '_syncronised' => 0,
   '_dst' => false,
))

Both items are correctly 7/6/2013. I guess it is getting messed up later in the code. The trace continues...

I added this:

echo "<pre>" . var_export($date->toString($format),true) . "</pre><br />\n";

And that had the output of: '6/7/2013', which is WRONG. So the problem is in how $date->toString($format) operates...

I'm now going through here to see what is off: http://framework.zend.com/manual/1.12/en/zend.date.constants.html#zend.date.constants.selfdefinedformats

Upvotes: 0

Views: 2544

Answers (1)

Morfie
Morfie

Reputation: 670

I fixed the issue!!!

The problem was with this:

$date = new Zend_Date($this->getDate());

It didn't like passing the DB Model's date into the Zend_Date constructor. Zend_Date was interpreting it incorrectly. After reading into the docs on Zend_Date, I learned that it prefers unix timestamps better.

I changed that line in getFormatted Date to look like this:

$date = new Zend_Date(strtotime($this->getDate()));

Now all the dates are working correctly!

Upvotes: 2

Related Questions