Special/Discount/Coupon End Date
Posted: Wed Jul 15, 2015 11:14 pm
The end date for specials, discounts and coupons is entered into the database as a date without a timestamp. This is the correct method for doing this.
When the admin user views the product list, the product controller checks today's date to see if it is between the start and end dates of specials/discounts and displays the correct price accordingly. If the end date is "today", the controller also does a time calculation to verify the time.
However, on the front-end, we have a different story. The special, discount and coupon end dates are retrieved directly from the database using the comparison "date_end > NOW()". The problem here is that the date does not include a timestamp, but NOW() does. So, if the end date for the special, discount or coupon is "today", a default timestamp of "00:00:00" is applied. This means that the special, discount or coupon end time is the beginning of the day, not the end of the day as expected. As a result, if the special, discount or coupon end date is "today", it will always be less than "NOW()". Subsequently, instead of specials, discounts and coupons ending at midnight "today" when they are supposed to end, they will end at midnight yesterday.
To get around this, the database query needs to have one day added to the end date, so that the specials, discounts or coupons end at the beginning of the next day.
The Fix
In catalog/model/catalog/product.php, the database queries in the "getProduct()" and "getProducts()" functions should have:
and
instead of:
and
respectively.
The "getProductSpecials()" function should have:
instead of:
And, the "getProductDiscounts()" function should have:
instead of:
The same should apply to the "getCoupon()" function in catalog/model/checkout/coupon.php.
When the admin user views the product list, the product controller checks today's date to see if it is between the start and end dates of specials/discounts and displays the correct price accordingly. If the end date is "today", the controller also does a time calculation to verify the time.
However, on the front-end, we have a different story. The special, discount and coupon end dates are retrieved directly from the database using the comparison "date_end > NOW()". The problem here is that the date does not include a timestamp, but NOW() does. So, if the end date for the special, discount or coupon is "today", a default timestamp of "00:00:00" is applied. This means that the special, discount or coupon end time is the beginning of the day, not the end of the day as expected. As a result, if the special, discount or coupon end date is "today", it will always be less than "NOW()". Subsequently, instead of specials, discounts and coupons ending at midnight "today" when they are supposed to end, they will end at midnight yesterday.
To get around this, the database query needs to have one day added to the end date, so that the specials, discounts or coupons end at the beginning of the next day.
The Fix
In catalog/model/catalog/product.php, the database queries in the "getProduct()" and "getProducts()" functions should have:
Code: Select all
DATE_ADD(pd2.date_end, INTERVAL 1 DAY) > NOW()Code: Select all
DATE_ADD(ps.date_end, INTERVAL 1 DAY) > NOW()Code: Select all
pd2.date_end > NOW()Code: Select all
ps.date_end > NOW()The "getProductSpecials()" function should have:
Code: Select all
DATE_ADD(ps.date_end, INTERVAL 1 DAY) > NOW()Code: Select all
ps.date_end > NOW()Code: Select all
DATE_ADD(date_end, INTERVAL 1 DAY) > NOW()Code: Select all
date_end > NOW()