7

I expected that the magento reporting (specifically, sales > orders report) would bucket days by local time.

This doesn't seem like it's the case, at least on magento 1.14.2.2. By comparing the raw orders with the order report, it is clear that some orders are not bucketed correctly in the report, because it's bucketing by UTC as opposed to local timezone.

The timezone is set correctly in the system, and lifetime statistics have been refreshed.

I have tried with Magento 1.9.2.2 and the result seems to be bucketed by local timezone. I guess this means it could be a configuration issue, or perhaps a bug?

Can anyone confirm or deny this issue?

  • I have found, that UTC is the timezone that is stored in the database. Then it is converted using the store configuration when it is shown to the customer/in the admin, etc. – RussellAlbin Jan 3 '16 at 16:33
  • Yeah but I guess the thing I'm wondering about is, when it's running a group by to get the totals, is it doing it on UTC or local time? The timestamp is truncated to a day bucket. – surjikal Jan 3 '16 at 18:39
  • From what I can tell all times in the database are utc. So the group by will be off UTC. Then when it's shown on page it's converted. I had to revert some code I did forcing the database time to central standard time during save because the reports were off by 5 hours. That's how I can to discover how it's on the database table. – RussellAlbin Jan 3 '16 at 18:43
2

I can confirm this behavior on 1.14.2.3, and it seems like it's a bug.

If you look at how the report aggregation works, you'll find that in Mage_Reports_Model_Resource_Report_Abstract::_getTableDateRangeSelect() the group by date is done like this:

$select  = $adapter->select()
    ->from(
        array($alias => $table),
        $adapter->getDatePartSql(
            $this->getStoreTZOffsetQuery(array($alias => $table), $alias . '.' . $column, $from, $to)
        )
    )
    ->distinct(true);

Where getStoreTZOffsetQuery() is supposed to do a timezone conversion from UTC to the current admin timezone.

I did not debug further to find out where it goes wrong, so unfortunately I don't have a fix for this either.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.