Repair WooCommerce core: products and catalog
WooCommerce products show on sale when they are not
The sale ended last week, or someone cleared the sale price in bulk, but the product page still shows the red sale badge and the strikethrough price. Shoppers add it to cart expecting the discount, then feel misled at checkout when the full price rings up. This is a caching problem, not a pricing problem. Here is why the flag gets stuck and a small script that recomputes it from the actual prices on every product.
The on sale badge comes from a cached flag, not a live check of the prices, and that cache falls behind when the daily sale cleanup cron misses, or a sale price is set or cleared through direct database edits or a bulk import. Run a small Python or Node.js script that reads each product's regular price, sale price, and sale date range from the WooCommerce REST API, works out whether it should really be on sale right now, and clears the stale sale price where it does not match. Full code, tests, and a dry run guard are below.
The problem in plain words
In WooCommerce, whether a product is "on sale" is not just a label. It is decided by comparing the sale price to the regular price and checking whether today falls inside the product's sale start and end dates. When all of that lines up, WooCommerce stores the sale price as the active price and marks the product as on sale, which is what makes the badge and the strikethrough appear on the shop and product pages.
The trouble is that this is computed once and then cached, in the product's price fields and in the lookup table WooCommerce uses to filter and sort the shop. The normal way that cache gets refreshed is a scheduled task called wc_scheduled_sales that runs once a day and clears out sale prices whose end date has passed. If that cron does not fire, or the store's prices were changed by a direct database update, a spreadsheet import, or a plugin that writes prices without going through WooCommerce's own save routine, the cache is never told to update. The product still carries a sale price and an active flag that no longer reflect reality.
Why it happens
The WooCommerce documentation describes wc_scheduled_sales as a daily cron that removes expired sale prices, and this depends on WordPress cron actually firing, which is itself a request-triggered system that can silently stop on a low traffic store. A few common ways the flag ends up wrong:
- WordPress cron never fires because the store gets little direct traffic and nothing triggers
wp-cron.php, so the daily sale sweep never runs. - A price import or a direct database update sets or clears
_sale_pricewithout also updating_priceand the product's row in the price lookup table, so the visible price and the cached flag disagree. - A sale is scheduled with a start and end date, but the theme or a caching layer reads the on sale flag from a cached fragment that was built before the sale started or after it ended.
- A plugin or a custom import sets
_sale_priceequal to or above_regular_price, which WooCommerce should treat as not on sale, but the stored flag was never recalculated to match.
This is a well documented WooCommerce core behavior, not a bug unique to one store. It is reported often on stores that rely on external price feeds or that changed prices outside of the normal admin save flow. See the citations at the end for the exact references.
The regular price, the sale price, and the sale date range are the source of truth. The on sale flag and the cached _price are just a snapshot of what those three fields implied the last time something recalculated them. A recompute script is a safety net that reads the real fields, works out the correct answer, and writes it back whenever the two disagree.
The fix, as a flow
We do not touch checkout or the storefront theme. We add a job that runs on a schedule, reads every product's regular price, sale price, and sale date range through the REST API, and works out with one pure function whether the product should currently be on sale. If the stored sale price disagrees with that answer, we correct it, which lets WooCommerce's own price and lookup table logic pick up the right value the next time it saves.
Build it step by step
Get access to the store
You need a WooCommerce REST API key pair, a consumer key and a consumer secret, with read and write access to products. Create it under WooCommerce, Settings, Advanced, REST API. Keep every value in environment variables, never in the file.
pip install requests
export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export DRY_RUN="true" # start safe, change to false to writenpm install node-fetch
export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export DRY_RUN="true" // start safe, change to false to writeList every simple and variable product
Page through the WooCommerce REST API's products endpoint. We keep the full list of fields we need for the decision: regular_price, sale_price, date_on_sale_from, date_on_sale_to, and the store's own on_sale flag so we can tell when it disagrees with reality.
import os, requests
from requests.auth import HTTPBasicAuth
WOO_URL = os.environ["WOO_STORE_URL"].rstrip("/")
AUTH = HTTPBasicAuth(os.environ["WOO_CONSUMER_KEY"], os.environ["WOO_CONSUMER_SECRET"])
def all_products():
page = 1
while True:
r = requests.get(
f"{WOO_URL}/wp-json/wc/v3/products",
params={"per_page": 50, "page": page, "status": "publish"},
auth=AUTH, timeout=30,
)
r.raise_for_status()
batch = r.json()
if not batch:
return
for product in batch:
yield product
page += 1const WOO_URL = process.env.WOO_STORE_URL.replace(/\/$/, "");
const AUTH = "Basic " + Buffer.from(
`${process.env.WOO_CONSUMER_KEY}:${process.env.WOO_CONSUMER_SECRET}`
).toString("base64");
async function* allProducts() {
let page = 1;
while (true) {
const res = await fetch(
`${WOO_URL}/wp-json/wc/v3/products?per_page=50&page=${page}&status=publish`,
{ headers: { Authorization: AUTH } }
);
if (!res.ok) throw new Error(`Woo products page ${page} returned ${res.status}`);
const batch = await res.json();
if (!batch.length) return;
for (const product of batch) yield product;
page++;
}
}Decide, with one pure function
Keep the decision in its own function that takes a product and the current time and returns an action. A pure function like this is easy to read and easy to test, which we do later. Money math stays in minor units, cents, to avoid floating point drift when comparing prices. The rule: a product should be on sale only when it has a sale price lower than the regular price, in minor units, and today falls inside the sale date range (an empty from or to date means no limit on that side). If the stored on_sale flag disagrees with that answer, or a stale sale price is still set past its end date, fix it.
from datetime import datetime, timezone
def to_minor(price_string):
"""Empty string or None means no price set."""
if price_string in (None, ""):
return None
return round(float(price_string) * 100)
def within_sale_window(date_from, date_to, now):
if date_from and now < datetime.fromisoformat(date_from):
return False
if date_to and now > datetime.fromisoformat(date_to):
return False
return True
def should_be_on_sale(product, now):
regular = to_minor(product.get("regular_price"))
sale = to_minor(product.get("sale_price"))
if regular is None or sale is None:
return False
if sale >= regular:
return False
return within_sale_window(product.get("date_on_sale_from"), product.get("date_on_sale_to"), now)
def decide(product, now):
expected = should_be_on_sale(product, now)
actual = bool(product.get("on_sale"))
if expected == actual:
return ("skip", "on sale flag already matches the prices")
if expected and not actual:
return ("fix", "product should be on sale but the flag says no")
# actual on_sale is stuck true, past the window or after an invalid sale price
return ("fix", "sale price is stale and should be cleared")export function toMinor(priceString) {
// Empty string, null, or undefined means no price set.
if (priceString === null || priceString === undefined || priceString === "") return null;
return Math.round(parseFloat(priceString) * 100);
}
export function withinSaleWindow(dateFrom, dateTo, now) {
if (dateFrom && now < new Date(dateFrom)) return false;
if (dateTo && now > new Date(dateTo)) return false;
return true;
}
export function shouldBeOnSale(product, now) {
const regular = toMinor(product.regular_price);
const sale = toMinor(product.sale_price);
if (regular === null || sale === null) return false;
if (sale >= regular) return false;
return withinSaleWindow(product.date_on_sale_from, product.date_on_sale_to, now);
}
export function decide(product, now) {
const expected = shouldBeOnSale(product, now);
const actual = Boolean(product.on_sale);
if (expected === actual) return ["skip", "on sale flag already matches the prices"];
if (expected && !actual) return ["fix", "product should be on sale but the flag says no"];
return ["fix", "sale price is stale and should be cleared"];
}Apply the correction the way the admin save would
When the action is fix and the product should not be on sale, clear sale_price so WooCommerce recalculates _price back to the regular price and updates the price lookup table on save. When it should be on sale but the flag says no, an empty PUT with the same sale price forces WooCommerce to resave and recompute the cached flag. Either way we never invent a price, we only nudge WooCommerce to recompute from the fields already on the product.
def apply_fix(product_id, expected_on_sale, current_sale_price):
if expected_on_sale:
payload = {"sale_price": current_sale_price}
else:
payload = {"sale_price": ""}
requests.put(
f"{WOO_URL}/wp-json/wc/v3/products/{product_id}",
json=payload, auth=AUTH, timeout=30,
).raise_for_status()async function applyFix(productId, expectedOnSale, currentSalePrice) {
const payload = expectedOnSale ? { sale_price: currentSalePrice } : { sale_price: "" };
const res = await fetch(`${WOO_URL}/wp-json/wc/v3/products/${productId}`, {
method: "PUT",
headers: { "Content-Type": "application/json", Authorization: AUTH },
body: JSON.stringify(payload),
});
if (!res.ok) throw new Error(`Woo product ${productId} update returned ${res.status}`);
}Wire it together with a dry run guard
The loop ties every piece together. Notice the dry run guard. On the first few runs, leave DRY_RUN on so the script only reports what it would change. Read the output, trust it, then switch it off to let it write. Run it on a schedule with cron once an hour, and also right after any bulk price import.
Always start with DRY_RUN=true. This script writes to real product prices, so you want to see its plan before it acts. Once the report looks right for a day, turn it off.
The full code
Here is the complete script in one file for each language. It reads settings from the environment, logs what it does, respects the dry run flag, and is safe to run again and again because it only touches products whose on sale flag disagrees with their own prices and dates.
View this code on GitHub Full runnable folder with tests in the woocommerce-fixes repo.
"""Recompute the on sale flag for WooCommerce products from their real prices.
The on sale badge and strikethrough price come from a cached flag that only updates
when WooCommerce resaves the product, usually through the daily wc_scheduled_sales
cron. If that cron is missed, or prices are changed outside the normal save path (a
direct database edit or a bulk import), the flag goes stale. This walks the catalog,
recomputes whether each product should be on sale right now, and corrects the ones
that disagree. Safe to run again and again. Run on a schedule.
"""
import os
import logging
import requests
from datetime import datetime, timezone
from requests.auth import HTTPBasicAuth
logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("recompute_on_sale")
WOO_URL = os.environ["WOO_STORE_URL"].rstrip("/")
AUTH = HTTPBasicAuth(os.environ["WOO_CONSUMER_KEY"], os.environ["WOO_CONSUMER_SECRET"])
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"
def to_minor(price_string):
"""Empty string or None means no price set."""
if price_string in (None, ""):
return None
return round(float(price_string) * 100)
def within_sale_window(date_from, date_to, now):
if date_from and now < datetime.fromisoformat(date_from):
return False
if date_to and now > datetime.fromisoformat(date_to):
return False
return True
def should_be_on_sale(product, now):
regular = to_minor(product.get("regular_price"))
sale = to_minor(product.get("sale_price"))
if regular is None or sale is None:
return False
if sale >= regular:
return False
return within_sale_window(product.get("date_on_sale_from"), product.get("date_on_sale_to"), now)
def decide(product, now):
expected = should_be_on_sale(product, now)
actual = bool(product.get("on_sale"))
if expected == actual:
return ("skip", "on sale flag already matches the prices")
if expected and not actual:
return ("fix", "product should be on sale but the flag says no")
return ("fix", "sale price is stale and should be cleared")
def all_products():
page = 1
while True:
r = requests.get(
f"{WOO_URL}/wp-json/wc/v3/products",
params={"per_page": 50, "page": page, "status": "publish"},
auth=AUTH, timeout=30,
)
r.raise_for_status()
batch = r.json()
if not batch:
return
for product in batch:
yield product
page += 1
def apply_fix(product_id, expected_on_sale, current_sale_price):
payload = {"sale_price": current_sale_price} if expected_on_sale else {"sale_price": ""}
requests.put(
f"{WOO_URL}/wp-json/wc/v3/products/{product_id}",
json=payload, auth=AUTH, timeout=30,
).raise_for_status()
def run():
now = datetime.now(timezone.utc).replace(tzinfo=None)
fixed = 0
for product in all_products():
action, reason = decide(product, now)
if action == "skip":
continue
expected = should_be_on_sale(product, now)
log.info(
"Product %s: %s. %s",
product["id"], reason, "would fix" if DRY_RUN else "fixing",
)
if not DRY_RUN:
apply_fix(product["id"], expected, product.get("sale_price"))
fixed += 1
log.info("Done. %d product(s) %s.", fixed, "to fix" if DRY_RUN else "fixed")
if __name__ == "__main__":
run()/**
* Recompute the on sale flag for WooCommerce products from their real prices.
*
* The on sale badge and strikethrough price come from a cached flag that only
* updates when WooCommerce resaves the product, usually through the daily
* wc_scheduled_sales cron. If that cron is missed, or prices are changed outside
* the normal save path (a direct database edit or a bulk import), the flag goes
* stale. This walks the catalog, recomputes whether each product should be on
* sale right now, and corrects the ones that disagree. Safe to run again and
* again. Run on a schedule.
*/
import { pathToFileURL } from "node:url";
const WOO_URL = (process.env.WOO_STORE_URL || "https://example.com").replace(/\/$/, "");
const AUTH = "Basic " + Buffer.from(
`${process.env.WOO_CONSUMER_KEY || "ck_dummy"}:${process.env.WOO_CONSUMER_SECRET || "cs_dummy"}`
).toString("base64");
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";
export function toMinor(priceString) {
if (priceString === null || priceString === undefined || priceString === "") return null;
return Math.round(parseFloat(priceString) * 100);
}
export function withinSaleWindow(dateFrom, dateTo, now) {
if (dateFrom && now < new Date(dateFrom)) return false;
if (dateTo && now > new Date(dateTo)) return false;
return true;
}
export function shouldBeOnSale(product, now) {
const regular = toMinor(product.regular_price);
const sale = toMinor(product.sale_price);
if (regular === null || sale === null) return false;
if (sale >= regular) return false;
return withinSaleWindow(product.date_on_sale_from, product.date_on_sale_to, now);
}
export function decide(product, now) {
const expected = shouldBeOnSale(product, now);
const actual = Boolean(product.on_sale);
if (expected === actual) return ["skip", "on sale flag already matches the prices"];
if (expected && !actual) return ["fix", "product should be on sale but the flag says no"];
return ["fix", "sale price is stale and should be cleared"];
}
async function* allProducts() {
let page = 1;
while (true) {
const res = await fetch(
`${WOO_URL}/wp-json/wc/v3/products?per_page=50&page=${page}&status=publish`,
{ headers: { Authorization: AUTH } }
);
if (!res.ok) throw new Error(`Woo products page ${page} returned ${res.status}`);
const batch = await res.json();
if (!batch.length) return;
for (const product of batch) yield product;
page++;
}
}
async function applyFix(productId, expectedOnSale, currentSalePrice) {
const payload = expectedOnSale ? { sale_price: currentSalePrice } : { sale_price: "" };
const res = await fetch(`${WOO_URL}/wp-json/wc/v3/products/${productId}`, {
method: "PUT",
headers: { "Content-Type": "application/json", Authorization: AUTH },
body: JSON.stringify(payload),
});
if (!res.ok) throw new Error(`Woo product ${productId} update returned ${res.status}`);
}
export async function run() {
const now = new Date();
let fixed = 0;
for await (const product of allProducts()) {
const [action, reason] = decide(product, now);
if (action === "skip") continue;
const expected = shouldBeOnSale(product, now);
console.log(`Product ${product.id}: ${reason}. ${DRY_RUN ? "would fix" : "fixing"}`);
if (!DRY_RUN) await applyFix(product.id, expected, product.sale_price);
fixed++;
}
console.log(`Done. ${fixed} product(s) ${DRY_RUN ? "to fix" : "fixed"}.`);
}
if (import.meta.url === pathToFileURL(process.argv[1]).href) {
run().catch((err) => { console.error(err); process.exit(1); });
}Add a test
The decision rule is the part most worth testing, because it decides which products get their price fields rewritten. Because we kept decide and shouldBeOnSale pure, the test needs no network and no WooCommerce store. It just feeds in plain objects and checks the action.
from datetime import datetime
from recompute_on_sale import decide, should_be_on_sale
NOW = datetime(2026, 7, 10, 12, 0, 0)
def product(**over):
base = {
"regular_price": "50.00",
"sale_price": "40.00",
"date_on_sale_from": None,
"date_on_sale_to": None,
"on_sale": False,
}
base.update(over)
return base
def test_fix_when_should_be_on_sale_but_flag_is_false():
p = product(on_sale=False)
assert decide(p, NOW)[0] == "fix"
assert should_be_on_sale(p, NOW) is True
def test_skip_when_flag_already_matches():
p = product(on_sale=True)
assert decide(p, NOW)[0] == "skip"
def test_fix_when_sale_window_has_passed_but_flag_still_true():
p = product(date_on_sale_to="2026-01-01T00:00:00", on_sale=True)
assert should_be_on_sale(p, NOW) is False
assert decide(p, NOW)[0] == "fix"
def test_skip_when_no_sale_price_and_flag_false():
p = product(sale_price="", on_sale=False)
assert decide(p, NOW)[0] == "skip"
def test_fix_when_sale_price_not_below_regular_but_flag_true():
p = product(sale_price="50.00", on_sale=True)
assert should_be_on_sale(p, NOW) is False
assert decide(p, NOW)[0] == "fix"
def test_skip_when_sale_starts_in_the_future_and_flag_false():
p = product(date_on_sale_from="2026-08-01T00:00:00", on_sale=False)
assert should_be_on_sale(p, NOW) is False
assert decide(p, NOW)[0] == "skip"import { test } from "node:test";
import assert from "node:assert/strict";
import { decide, shouldBeOnSale } from "./recompute-on-sale.js";
const NOW = new Date("2026-07-10T12:00:00Z");
const product = (over = {}) => ({
regular_price: "50.00",
sale_price: "40.00",
date_on_sale_from: null,
date_on_sale_to: null,
on_sale: false,
...over,
});
test("fix when should be on sale but flag is false", () => {
const p = product({ on_sale: false });
assert.equal(decide(p, NOW)[0], "fix");
assert.equal(shouldBeOnSale(p, NOW), true);
});
test("skip when flag already matches", () => {
assert.equal(decide(product({ on_sale: true }), NOW)[0], "skip");
});
test("fix when sale window has passed but flag still true", () => {
const p = product({ date_on_sale_to: "2026-01-01T00:00:00Z", on_sale: true });
assert.equal(shouldBeOnSale(p, NOW), false);
assert.equal(decide(p, NOW)[0], "fix");
});
test("skip when no sale price and flag false", () => {
assert.equal(decide(product({ sale_price: "", on_sale: false }), NOW)[0], "skip");
});
test("fix when sale price not below regular but flag true", () => {
const p = product({ sale_price: "50.00", on_sale: true });
assert.equal(shouldBeOnSale(p, NOW), false);
assert.equal(decide(p, NOW)[0], "fix");
});
test("skip when sale starts in the future and flag false", () => {
const p = product({ date_on_sale_from: "2026-08-01T00:00:00Z", on_sale: false });
assert.equal(shouldBeOnSale(p, NOW), false);
assert.equal(decide(p, NOW)[0], "skip");
});Case studies
The sale that never turned off
A small store scheduled a two week sale, but between visits WordPress cron went quiet for eleven days because nothing hit the site to trigger it. The sale end date passed, yet every product kept its badge and its strikethrough price for almost two weeks after the discount should have ended.
The recompute script found forty three products with a stale sale price still set past their end date, ran in dry run to confirm the list, then cleared them for real in one pass.
The import that skipped the recalculation
A supplier feed updated prices twice a day through a direct database write for speed, bypassing WooCommerce's normal product save. Some products ended up with a sale price left over from a previous run, still marked on sale even though the new regular price made the old sale price higher than the new price.
Running the script hourly right after each import kept the on sale flag matching the freshest prices, without slowing down the import itself.
After this runs on a schedule, a missed cron or an import that bypasses WooCommerce's save routine is no longer a pricing surprise at checkout. The worst case becomes a short delay of an hour before the flag catches up. Keep it running even after you fix the root cause, because prices will always be touched outside the normal path once in a while.
FAQ
Why does WooCommerce still show a product as on sale after the sale ended?
WooCommerce decides the on sale badge from the sale price field and the sale end date together, and that state is cached in the price lookup table. If the daily cron that clears expired sale prices does not run, or a price is edited directly in the database or through an import, the cached flag and the real prices fall out of sync and the badge keeps showing.
Is it safe to fix the on sale flag with a script?
Yes, when the script only recomputes the flag from the product's own regular price, sale price, and sale date fields, and never invents a new price. Start in dry run mode to see exactly which products would change before it writes anything.
How often should this recompute job run?
Once an hour is enough for most stores, and right after any bulk price import or scheduled sale change. It only touches products whose computed on sale state does not match what is stored, so running it often is safe.
Related field notes
Citations
On the problem:
- WooCommerce code reference:
wc_scheduled_sales, the daily cron that clears expired sale prices. woocommerce.github.io/code-reference - WordPress developer docs: how WP-Cron depends on site visits and can silently stop firing on low traffic sites. developer.wordpress.org/plugins/cron
- WooCommerce documentation: how sale price, sale dates, and the on sale state are configured on a product. woocommerce.com/document/managing-products
On the solution:
- WooCommerce REST API: retrieve and update a product, including
sale_price,date_on_sale_from,date_on_sale_to, andon_sale. woocommerce.github.io/woocommerce-rest-api-docs - WooCommerce code reference:
WC_Product::is_on_sale()and how the active price is chosen from the regular and sale price. woocommerce.github.io/code-reference/classes/WC-Product - WooCommerce developer blog: the lookup table used to cache pricing and stock state for fast catalog queries. developer.woocommerce.com/docs/product-lookup-tables
Stuck on a tricky one?
If you have a bug in WooCommerce, WooCommerce Subscriptions, or the WooCommerce Stripe gateway that you would rather hand off, this is the kind of work I do. Message me and we can work through it together.
Did this fix your sale badges?
If this saved you a confused customer or a pile of "why was I charged full price" messages, you can buy me a coffee. It is the best way to keep these field notes free and growing.
Buy me a coffee on Ko-fi