opencart/opencart — pull request #2225 by CoverUp (2014-11-07 12:15:58+00:00)
Title: Update product.php

I have noted that sometimes recently added products are not displayed at all, so you start to figure out whats wrong... you check the database and the new item is there, everythings look good but your new added products just don't appear in your category or categories. well after a looooong checking i found the cause:
MySQL date could be different from your php date, so when you add a product with the current date, the product is saved with that date in the field date_available.
But then, when you reload the page to see your new added product in your site, the system use the mysql date instead the php date, and if the mysql date is several hours behind the php date the product don't appear because the operation p.date_available <= NOW() is not meet.

Sadly most of the webhosts offer only a cpanel without shell, so you can't configure mysql timezone in order to match php timezone

Thus, in order to avoid this particular issue i propose to change NOW() to date(), the result is the same but also solve this issue.

You can replicate the issue as follow:

mysql timezone set with default +0:00
php timezone set with +2:00
php time is the same as your PC time
thus
your pc date/time: 2014-07-11 01:00 am
php date/time: 2014-07-11 01:00 am
mysql date/time: 2014-07-10 23:00 pm

at 1:01 yo add a new product as always
at 1:10 you try to see the new product but is not displayed
cause: 2014-07-10 from NOW() is compared with 2014-07-11 saved from date()

please note that this issue only appear if you add products after midnight, it's like a bad nightmare :ghost:

The solution:
changed NOW() (with current date/time from mysql) to date('Y-m-d') (with current date from php) to solve issues found when timezone on mysql are not equal to timezone on php

pd: only yyyy-mm-dd is saved on the DB during product add, that's why we use date(Y-m-d) as a replacement of NOW()