cancel
Showing results for 
Search instead for 
Did you mean: 

Wrong rule date on catalog rules

Wrong rule date on catalog rules

On Magento 1.9.0.1 we have problem with crons, but with set some script call tasks ('catalogrule_apply_all', 'catalog_product_index_price_reindex_all') it possible work. But one thing is wrong. In table 'catalogrule_product_price' the set rule_date is wrong.

As I see here and as result in DB, cron write a row for curent day and next one (or only for next one and previous stay). But in our DB it start from next day. As date is offset +1 day (field rule_price).

Example for period latest_start_date=2015-06-10 and earliest_end_date=2015-06-12, in DB make rules for 2015-06-11, 2015-06-12, 2015-06-13 and not for correct 2015-06-10, 2015-06-11, 2015-06-12.

The time here is before UTC.

Added latter message:

It is possible to be some timezone problem. As example DB timezone is different from PHP script timezone.

I see it use in SELECT object:

SELECT DATE_ADD(FROM_UNIXTIME(1434056400), INTERVAL -1 DAY) AS rule_date
SELECT FROM_UNIXTIME(1434056400) AS rule_date
SELECT DATE_ADD(FROM_UNIXTIME(1434056400), INTERVAL 1 DAY) AS rule_date

And some:

(UNIX_TIMESTAMP(dates.rule_date) >= from_time)
AND (IF((to_time = 0), 1, UNIX_TIMESTAMP(dates.rule_date) <= to_time))

But from_time is exact UTC day start and UNIX_TIMESTAMP on rule_date is not - is with timezone offset and day start for timezone

Added latter message:

It was exactly that.

After set next it worked correct.

$sql_read=Mage::getSingleton('core/resource')->getConnection('core_read');
$sql_read->query("SET @@session.time_zone='+00:00'");//Can not set 'UTC' here
//Here is execution tasks
$sql_read->query("SET @@session.time_zone='SYSTEM'");//Back set previous state. By me 'SYSTEM'.

I don't like it really, to change time_zone global - can be for longer time. And standard PHP save connection to DB and possible use same session.

If someone as example from Magento have better solution, please write here. For me is a bug in Magento to use set from PHP timestamps for UTC date and to convert with MySQL date to timestamp (have offset) and compare. For me next is bug:

(UNIX_TIMESTAMP(dates.rule_date) >= from_time)
AND (IF((to_time = 0), 1, UNIX_TIMESTAMP(dates.rule_date) <= to_time))

Added latter message:

I fix it direct in broken code in class Mage_CatalogRule_Model_Action_Index_Refresh on 2 places

From:
$this->_connection->getUnixTimestamp('dates.rule_date')
To:
$this->_connection->getUnixTimestamp("CONVERT_TZ(dates.rule_date,'+00:00',@@session.time_zone)")

Same for includes/src/Mage_CatalogRule_Model_Action_Index_Refresh.php if you not generate them new.