Magento 1.9 – Fix Date Time Picker and Time Zone Issues

adminhtmldatepickermagento-1.9

I've got a custom datetime attribute on a product that allows a date AND time to be selected. The field uses the Magento Date picker with the time fields available.

If I select a date and a time, and save, it stores the selected date and time in the database. However, on the product grid it shows the time portion an hour out.

This will be because I'm in the BST timezone (GMT+1), which is also what the store is set to.

Example: I select '2017-07-20 17:00:00' in the datepicker (actually
its '20/7/2017 17:00' but that gets converted in the back-end model).
This is then stored in the database as '2017-07-20 17:00:00', which I
believe is the correct UTC/GMT value. The catalog grid view then shows
the column with the date '2017-07-20 18:00:00'.

I'm not sure which part of this is at fault, or where to fix it. Is it:

  • a) Saving the date in the db is wrong, and I should convert from current timezone to UTC before saving.

  • b) The datepicker is not using my browser's timezone?

  • c) The catalogue grid is forcing timezone conversion?

I've tried point a), so that it saves the date as -1 hour, but then the datepicker time is wrong when editing the product.

Suggestions welcome.

Best Answer

I have just run into this issue. By placing a test magento order, and confirming the datetime placed for the created_at value of that order, I have managed to establish the following:

The date from the form field (thus date picker attached to that field) is not being converted to/from UTC when loading / saving on the db.

The display in the grid is correctly converting from UTC to the sites locale timezone.

Example: I place my order at 08:22 am (AWST) - the entry saved in created at was 00:22 (-8h UTC from AWST) When that order is viewed in the grid, it is correctly shown as 08:22 AM again (AWST)

Now, If I place a date time in the date-picker (or just manually type it into the form field) (so again using 08:22 AM), the value stored into the DB is exactly that 08:22 - where it should have been 00:22 - so, it is not converted to UTC upon save.

If I do convert it to UTC before save:

$data['date_time'] = Mage::getModel('core/date')->gmtDate("Y-m-d H:i:s", $data['date_time']);

it is correctly save to UTC in db, and the grid view then correctly displays 08:22 AM (correctly converted from UTC to AWST) However, if I view that in the form edit, it simply displays the value AS IS from the db - so as 00:22 - so no conversion was done from UTC to AWST (which is part of the issue)

To make this work for me, I had to:

Create a new form element:

$form->addType('datetimezone', 'Enjo_Events_Lib_Data_Form_Element_Datetime');

created the element as such:

$dateFormatIso = Mage::app()->getLocale()->getDateTimeFormat(Mage_Core_Model_Locale::FORMAT_TYPE_SHORT);
        $form->addField('date_time', 'datetimezone', array(
            'name'   => 'date_time',
            'label'  => Mage::helper('enjo_events')->__('Date and Time'),
            'title'  => Mage::helper('enjo_events')->__('Date and Time'),
            'image'  => $this->getSkinUrl('images/grid-cal.gif'),
            'input_format' => Varien_Date::DATETIME_INTERNAL_FORMAT,
            'format' => $dateFormatIso,
            'time' => true,
            'class'     => 'required-entry',
            'required'  => true,
            'width'     => "150",
            'locale'    => Mage::getStoreConfig('general/locale/code', 0),
            'convert_date_format' => "d/m/Y h:m a"
        ));

and the new form element code:

class Enjo_Events_Lib_Data_Form_Element_Datetime extends Varien_Data_Form_Element_Datetime
{

    /**
     * Get date value as string.
     * Format can be specified, or it will be taken from $this->getFormat()
     *
     * @param string $format (compatible with Zend_Date)
     * @return string
     */
    public function getValue($format = null)
    {
        if (empty($this->_value)) {
            return '';
        }
        if (null === $format) {
            $format = $this->getFormat();
        }
        return Mage::getModel('core/date')->date($this->getConvertDateFormat(),  $this->_value);
    }
}

and on the controller action, convert the date to UTC before saved.

$data = $this->getRequest()->getParams(); $data = $this->_filterDateTime($data, array('date_time')); $data['date_time'] = Mage::getModel('core/date')->gmtDate("Y-m-d H:i:s", $data['date_time']); $event->addData($data)->save();

which resulted in the correct data: UTC in DB, my store lcoale/zone in edits / views of said data.

I am not 100% sure where the failing is, so I class this as a workaround.

Related Topic