Commit 99b70f52eec for woocommerce
commit 99b70f52eec55986ebb505be4b686b0319034734
Author: Cvetan Cvetanov <cvetan.cvetanov@automattic.com>
Date: Thu Aug 27 18:59:41 2026 +0300
Fix day-precision date queries ignoring the site timezone (#67903)
* Fix day-precision date queries anchoring on UTC instead of local midnight
`parse_date_for_wp_query()` built the day boundaries for meta date queries by
passing a local-midnight timestamp through `gmdate()` and back through
`strtotime()`. `gmdate()` re-derives a UTC calendar date, and `strtotime()` runs
under PHP's default timezone, which WordPress forces to UTC, so both steps threw
away the local anchor that `wc_string_to_datetime()` had already established.
A store in America/New_York asking `wc_get_orders( array( 'date_paid' =>
'2026-07-20' ) )` therefore missed an order paid at 21:00 that evening and
instead found it under '2026-07-21'. The upper bound added a fixed 86400
seconds, which is also wrong on the two days a year a local day is 23 or 25
hours long.
Rebuild both bounds in `wp_timezone()` from the local calendar date, and derive
the upper one from the next local midnight. The bounds are now half-open, which
also brings the comparison operators in line with HPOS: `>` was excluding the
next midnight instant, `<=` was including it, and a `...` range was collapsing
its final day down to that day's first second.
Refs #39462
* Add changelog entry for the day-precision date query timezone fix
* test: cover single-day date ranges and scope the HPOS parity claim
The half-open bounds this branch introduces change the `...` range upper bound
the most: it is now the midnight after the final day rather than that day's own
midnight, which previously collapsed a single-day range down to one instant.
Nothing pinned that, so `2026-07-20...2026-07-20` could regress back to matching
almost nothing without a test noticing.
Add the case to both the boundary provider and the `wc_get_orders()` provider.
`WC_Order_Date_Query_Test` also asserted cross-backend agreement without saying
where that agreement stops. HPOS still derives its upper bound from a fixed
`DAY_IN_SECONDS` in `OrdersTableQuery::local_time_to_gmt_date_query()`, so the
two backends diverge by an hour on a 23- or 25-hour day and the assertion only
holds for ordinary ones. Say so in the class docblock and the testdox rather
than leaving a claim the suite does not cover.
Refs #39462
* test: skip DST cases the runtime's timezone database cannot exercise
PHP resolves timezones against the tz database compiled into the binary rather
than the one the OS ships, and the CI matrix runs the unit suite on PHP 7.4,
which carries tzdata 2022.1. Egypt reinstated DST in tzdata 2023a, so on that
job 2026-04-24 in Cairo is still an ordinary 24-hour day: the code returns
1777068000 for the upper bound while the test expects 1777064400, and
test_day_precision_meta_boundaries_follow_dst_transitions fails for a reason
that has nothing to do with the change under test. It passes locally on PHP
8.1 (tzdata 2023.3), which is why it went unnoticed.
Skip a case when the runtime puts no transition on the day it names. The
expected timestamps stay hard-coded, since a literal is a stronger assertion
than one recomputed through the same API the production code uses; only the
runtimes that cannot exercise them opt out. Both America/New_York cases keep
their transitions under tzdata 2022.1 and still run everywhere.
Also record the range-widening in the changelog. A `start...end` query now
covers the whole of its final day where it previously collapsed that day to a
single second, so a store reading the changelog learns that ranges return an
extra day rather than discovering it from its own reports.
Refs #39462
* test: pin the missing-midnight case to a zone with a stable DST rule
Africa/Cairo was the wrong fixture for "DST starts at 00:00, so local midnight
never happens". Egypt has repealed and reinstated DST repeatedly, most recently
in tzdata 2023a, so the case only holds on a runtime new enough to know about
it. Under PHP 7.4 (tzdata 2022.1) the day is an ordinary 24 hours, which is why
the previous commit had to skip it there and lost the coverage on that job.
Asia/Beirut 2026-03-29 has the same shape, a 23-hour day beginning at 01:00
+03:00, and resolves identically under tzdata 2022.1 and 2023.3. Its timestamps
are also distinct from the America/New_York ones, so the three DST rows no
longer share values and cannot be confused for each other.
Keep the guard as a backstop rather than removing it. Timezone policy is set by
governments and any zone can change; the guard turns that into a skip instead
of a failure that reads like a regression in this code.
Also switch a string to single quotes, which phpcs flagged on the line the
previous commit added.
Refs #39462
* fix: resolve offset-bearing date query vars to the day HPOS reads them as
Anchoring day boundaries on the local calendar date fixed bare dates and naive
datetimes, but it also changed how a string carrying its own timezone is read.
`wc_string_to_datetime()` resolves `2026-07-20T02:00:00Z` to a true instant and
returns it in the site zone, so on America/New_York the local date is the 19th
while `OrdersTableQuery` names the same input the 20th. Post storage and HPOS
came out with adjacent, non-overlapping windows for that query, where before
this branch they overlapped on 20 of 24 hours.
Such a string reaches the day-precision branch: the operator regex cannot match
it, so it falls through to the arm that sets day precision unconditionally.
Resolve the named day through `get_date_query_day()`, which keeps the local date
for inputs that name a day and uses the UTC date for inputs that name an instant,
the way HPOS already does. Both backends now agree on bare dates, naive
datetimes, and both explicit-designator forms.
Refs #39462
* test: guard the order storage toggle in the date query parity test
`WC_Order_Date_Query_Test` flips the authoritative order storage twice per
provider case without installing `wc_allow_changing_orders_storage_while_sync_is_pending`.
`CustomOrdersTableController::process_pre_update_option()` throws when any order
is pending sync, and the HPOS tables outlive the per-test transaction, so a row
left behind by an earlier test turns this suite red for a reason unrelated to
what it asserts. Every other storage-toggling suite in the tree installs that
filter.
Also assert the storage actually swapped. `toggle_cot_feature_and_usage()` only
checks that through `assert()`, which is compiled out when `zend.assertions` is
off, and a toggle that silently no-ops would leave the parity assertion
comparing post storage against itself.
Refs #39462
* refactor: name the query day with the same expression HPOS uses
`get_date_query_day()` branched on whether the query var carried its own timezone
designator, because `wc_string_to_datetime()` leaves the parsed instant in
`getTimestamp()` on one path and in `getOffsetTimestamp()` on the other. Both
branches reduce to `gmdate( 'Y-m-d', wc_string_to_timestamp( $raw ) )`, which is
the expression `OrdersTableQuery::local_time_to_gmt_date_query()` already uses.
Inline it. The posts and HPOS backends now derive the day from the same rule
rather than from two separately written ones that happen to agree, and the
duplicated ISO-8601 regex goes away. That regex had to stay byte-identical to the
one inside `wc_string_to_datetime()` or the backends would silently split again.
Verified identical for bare dates, naive datetimes, `Z` and `+05:00` designators,
and ranges. The removed helper also claimed to close the posts/HPOS day split
generally, which was too broad: `date_created` and `date_modified` take the
`post_date` branch and still name the local day. Say so in the parity test instead
of implying the whole class of queries is covered.
Removes one baselined `$dates[1]` offset error along with the second call site.
Refs #39462
* fix: keep an unparseable day query var from fataling the query
The day boundaries are built below the try/catch that guards the rest of the
date parsing in this method, so a query var that lands past year 9999 threw out
of `wc_get_orders()` instead of returning no date constraint. `'+8000 years'`
resolves to `10026-08-26`, which `DateTime` refuses to parse. The pre-fix code
fed the same input through `strtotime()`, which returned false and simply
matched nothing.
Wrap the boundary construction so the method keeps its existing contract: a date
it cannot make sense of drops the constraint rather than surfacing as a fatal on
a public data store method.
Also correct the comment above it. It said the day comes from the UTC calendar
date of the parsed instant, but the code reads the raw string with a bare
`strtotime()` and never touches `$dates`. Those differ for a naive datetime,
which `wc_string_to_datetime()` reads as local time first, so the comment as
written invited exactly the edit that would break posts/HPOS parity.
Refs #39462
* test: pin the guard for a date the calendar cannot represent
The guard that keeps an out-of-range day query var from throwing had no test, so
a later refactor could drop it and nothing would fail. `'+8000 years'` is the
cheapest input that reaches it: `wc_string_to_datetime()` succeeds, because
`get_gmt_from_date()` falls back to the epoch when `date_create()` rejects the
year, but the boundary construction still resolves to 10026-08-26 and throws.
Assert the query comes back with no date constraint rather than raising.
Refs #39462
* fix: make an unrepresentable day query var match nothing, not everything
The guard added for dates beyond year 9999 returned the query args untouched.
Both callers strip their own clause for the key before calling
`parse_date_for_wp_query()`, so coming back with an empty `meta_query` does not
mean "no bounds", it means no date filter at all: `wc_get_orders()` with
`'date_paid' => '+8000 years'` returned every order. An export or reconciliation
job passing a bad date would silently process the whole table.
Emit a clause that cannot match instead. The comparison is typed `NUMERIC` so it
does not depend on the lexicographic ordering the untyped clauses inherit, and
`''`, `0` and real timestamps all cast to a value that is not below zero.
HPOS already resolves the same input to an epoch-anchored window and returns
nothing, so this brings post storage back in line rather than inventing a third
behaviour.
The unit test now pins the clause rather than the empty array it previously
codified, and the parity suite covers the observable `wc_get_orders()` result on
both storage backends.
Refs #39462
* fix: use an empty range for an unrepresentable day, not a negative bound
The fallback clause was `meta_value < 0`, which assumed no stored timestamp is
negative. WooCommerce persists dates before 1970 as negative numbers, so an order
paid 1960-01-01 still matched `'date_paid' => '+8000 years'` on post storage while
HPOS returned nothing. The clause meant to match nothing matched exactly the rows
most likely to be wrong.
Use an empty range instead: no value can be both at least 1 and at most 0, which
holds for negative timestamps, zero, and an unset key alike, and does not depend
on the stored value's sign.
Cover it with an order paid before the epoch, on both storage backends, since the
existing case pays in 2026 and would pass against the old bound too.
Refs #39462
* style: split the storage backend map across lines
`lint:changes:branch` rejects a multi-item array with explicit keys declared on
one line. Use the same shape the sibling test in this file already uses.
Refs #39462
* docs: trim the day-precision comments to what the code cannot say
The comment block above the boundary construction had grown to sixteen lines for
six lines of code, restating what the expressions already show and explaining the
throw well before the catch that handles it.
Keep only the parts a reader cannot get from the code: that the day must be named
from the raw string rather than from `$dates` or the backends diverge, why the
fallback is an empty range rather than no clause or a one-sided bound, and why the
upper bound is `tomorrow` rather than a fixed 86400.
The parity suite's docblock now cites #68060 for the HPOS DST gap instead of
restating it.
Refs #39462
* docs: tighten the day-precision comments further
Second pass over the same three blocks, dropping the clauses the expressions
already show: that the bounds are anchored on local midnight, and that the upper
bound is the next local midnight.
Refs #39462
* docs: say what the day-precision comments mean, not what they rule out
Both comments were trimmed past the point of being readable. "Not a fixed 86400"
leaves the reader to work out that 86400 is 24 hours, and the catch comment led
with the alternatives it rejects rather than with the impossible range it builds,
so the mechanism had to be reverse engineered from the clause.
Lead with what each does, then keep the rejected alternatives as the reason.
Refs #39462
diff --git a/plugins/woocommerce/changelog/fix-wc-get-orders-day-precision-timezone b/plugins/woocommerce/changelog/fix-wc-get-orders-day-precision-timezone
new file mode 100644
index 00000000000..a28fa461364
--- /dev/null
+++ b/plugins/woocommerce/changelog/fix-wc-get-orders-day-precision-timezone
@@ -0,0 +1,4 @@
+Significance: patch
+Type: fix
+
+Anchor day-precision date queries on the store's local midnight instead of UTC midnight, so wc_get_orders() and wc_get_products() match the day a merchant means when filtering on date_paid, date_completed, date_on_sale_from and date_on_sale_to. A `start...end` range now covers the whole of its final day, where before it collapsed that day down to a single second, so range queries return one more day of results than they used to.
diff --git a/plugins/woocommerce/includes/data-stores/class-wc-data-store-wp.php b/plugins/woocommerce/includes/data-stores/class-wc-data-store-wp.php
index 82428a59f55..387cf81eebe 100644
--- a/plugins/woocommerce/includes/data-stores/class-wc-data-store-wp.php
+++ b/plugins/woocommerce/includes/data-stores/class-wc-data-store-wp.php
@@ -350,6 +350,10 @@ class WC_Data_Store_WP {
$dates = array();
$operator = '=';
+ // The raw strings $dates[0] and $dates[1] came from. Only day precision reads them.
+ $raw_start = '';
+ $raw_end = '';
+
try {
// Specific time query with a WC_DateTime.
if ( is_a( $query_var, 'WC_DateTime' ) ) {
@@ -358,7 +362,11 @@ class WC_Data_Store_WP {
$dates[] = new WC_DateTime( "@{$query_var}", new DateTimeZone( 'UTC' ) );
} elseif ( preg_match( $query_parse_regex, $query_var, $sections ) ) { // Query with operators and possible range of dates.
if ( ! empty( $sections[1] ) ) {
- $dates[] = is_numeric( $sections[1] ) ? new WC_DateTime( "@{$sections[1]}", new DateTimeZone( 'UTC' ) ) : wc_string_to_datetime( $sections[1] );
+ $dates[] = is_numeric( $sections[1] ) ? new WC_DateTime( "@{$sections[1]}", new DateTimeZone( 'UTC' ) ) : wc_string_to_datetime( $sections[1] );
+ $raw_start = $sections[1];
+ $raw_end = $sections[3];
+ } else {
+ $raw_start = $sections[3];
}
$operator = in_array( $sections[2], $valid_operators, true ) ? $sections[2] : '';
@@ -369,6 +377,7 @@ class WC_Data_Store_WP {
}
} else { // Specific time query with a string.
$dates[] = wc_string_to_datetime( $query_var );
+ $raw_start = $query_var;
$precision = 'day';
}
} catch ( Exception $e ) {
@@ -442,15 +451,62 @@ class WC_Data_Store_WP {
// Meta dates are stored as timestamps in the db.
// Check against beginning/end-of-day timestamps when using 'day' precision.
if ( 'day' === $precision ) {
- $start_timestamp = strtotime( gmdate( 'm/d/Y 00:00:00', $dates[0]->getTimestamp() ) );
- $end_timestamp = '...' !== $operator ? ( $start_timestamp + DAY_IN_SECONDS ) : strtotime( gmdate( 'm/d/Y 00:00:00', $dates[1]->getTimestamp() ) );
+ /*
+ * Name the day from the raw string the way OrdersTableQuery::local_time_to_gmt_date_query()
+ * does, never from the timezone-aware $dates, or posts and HPOS resolve one query var to
+ * different days.
+ */
+ try {
+ $timezone = wp_timezone();
+ $start = new DateTime( gmdate( 'Y-m-d', (int) wc_string_to_timestamp( $raw_start ) ) . ' 00:00:00', $timezone );
+ $end = '...' === $operator
+ ? new DateTime( gmdate( 'Y-m-d', (int) wc_string_to_timestamp( $raw_end ) ) . ' 00:00:00', $timezone )
+ : clone $start;
+ } catch ( Exception $e ) {
+ /*
+ * The date could not be constructed, which a year past 9999 does. Match nothing, by
+ * asking for a value that is both at least 1 and at most 0.
+ *
+ * Returning without a clause would not do that: the callers have already dropped
+ * their own date clause, so the query would come back unfiltered. Nor would a single
+ * bound like "below 0", since these keys hold a negative timestamp for a date before
+ * 1970.
+ */
+ $wp_query_args['meta_query'][] = array(
+ 'key' => $key,
+ 'value' => array( 1, 0 ),
+ 'type' => 'NUMERIC',
+ 'compare' => 'BETWEEN',
+ );
+
+ return $wp_query_args;
+ }
+
+ /*
+ * The end of the day is the next local midnight, not the start plus 24 hours: a local day
+ * runs 23 or 25 hours across a DST transition. '+1 day' would not do either, because it
+ * keeps the start's time of day, and that is not midnight in a timezone where DST begins
+ * at 00:00 and the constructor above had to move the start forward.
+ */
+ $end->modify( 'tomorrow' );
+
+ $start_timestamp = $start->getTimestamp();
+ $end_timestamp = $end->getTimestamp();
+
+ // The bounds are half-open (>= start, < end) so that a day is matched in full and exactly once.
switch ( $operator ) {
case '>':
+ $wp_query_args['meta_query'][] = array(
+ 'key' => $key,
+ 'value' => $end_timestamp,
+ 'compare' => '>=',
+ );
+ break;
case '<=':
$wp_query_args['meta_query'][] = array(
'key' => $key,
'value' => $end_timestamp,
- 'compare' => $operator,
+ 'compare' => '<',
);
break;
case '<':
@@ -470,7 +526,7 @@ class WC_Data_Store_WP {
$wp_query_args['meta_query'][] = array(
'key' => $key,
'value' => $end_timestamp,
- 'compare' => '<=',
+ 'compare' => '<',
);
}
} elseif ( '...' !== $operator ) {
diff --git a/plugins/woocommerce/phpstan-baseline.neon b/plugins/woocommerce/phpstan-baseline.neon
index 1fddb9acec8..27faa90dba0 100644
--- a/plugins/woocommerce/phpstan-baseline.neon
+++ b/plugins/woocommerce/phpstan-baseline.neon
@@ -17181,7 +17181,7 @@ parameters:
-
message: '#^Offset 1 might not exist on array\{0\: WC_DateTime, 1\?\: WC_DateTime\}\.$#'
identifier: offsetAccess.notFound
- count: 2
+ count: 1
path: includes/data-stores/class-wc-data-store-wp.php
-
diff --git a/plugins/woocommerce/tests/php/includes/data-stores/class-wc-data-store-wp-test.php b/plugins/woocommerce/tests/php/includes/data-stores/class-wc-data-store-wp-test.php
index 679b1e115b1..31a960523f3 100644
--- a/plugins/woocommerce/tests/php/includes/data-stores/class-wc-data-store-wp-test.php
+++ b/plugins/woocommerce/tests/php/includes/data-stores/class-wc-data-store-wp-test.php
@@ -5,6 +5,82 @@
*/
final class WC_Data_Store_WP_Test extends WC_Unit_Test_Case {
+ /**
+ * Timestamp of 2026-07-19 00:00:00 -04:00, in America/New_York.
+ */
+ private const NY_JUL_19_MIDNIGHT = 1784433600;
+
+ /**
+ * Timestamp of 2026-07-20 00:00:00 -04:00, in America/New_York.
+ */
+ private const NY_JUL_20_MIDNIGHT = 1784520000;
+
+ /**
+ * Timestamp of 2026-07-21 00:00:00 -04:00, in America/New_York.
+ */
+ private const NY_JUL_21_MIDNIGHT = 1784606400;
+
+ /**
+ * Timestamp of 2026-07-26 00:00:00 -04:00, in America/New_York.
+ */
+ private const NY_JUL_26_MIDNIGHT = 1785038400;
+
+ /**
+ * Timestamp of 2026-11-01 00:00:00 -04:00, the last day of DST in America/New_York.
+ */
+ private const NY_NOV_01_MIDNIGHT = 1793505600;
+
+ /**
+ * Timestamp of 2026-11-02 00:00:00 -05:00, 25 hours after the start of 2026-11-01.
+ */
+ private const NY_NOV_02_MIDNIGHT = 1793595600;
+
+ /**
+ * Timestamp of 2026-03-08 00:00:00 -05:00, the day DST starts in America/New_York.
+ */
+ private const NY_MAR_08_MIDNIGHT = 1772946000;
+
+ /**
+ * Timestamp of 2026-03-09 00:00:00 -04:00, 23 hours after the start of 2026-03-08.
+ */
+ private const NY_MAR_09_MIDNIGHT = 1773028800;
+
+ /**
+ * Timestamp of the first instant of 2026-03-29 in Asia/Beirut. DST starts at 00:00 that day,
+ * so local midnight never happens and the day starts at 01:00:00 +03:00 instead.
+ */
+ private const BEIRUT_MAR_29_START = 1774735200;
+
+ /**
+ * Timestamp of 2026-03-30 00:00:00 +03:00, 23 hours after the start of 2026-03-29.
+ */
+ private const BEIRUT_MAR_30_MIDNIGHT = 1774818000;
+
+ /**
+ * The System Under Test.
+ *
+ * @var WC_Data_Store_WP
+ */
+ private $sut;
+
+ /**
+ * Set up test fixtures.
+ */
+ public function setUp(): void {
+ parent::setUp();
+ $this->sut = new WC_Data_Store_WP();
+ }
+
+ /**
+ * Restore the default timezone settings.
+ */
+ public function tearDown(): void {
+ update_option( 'timezone_string', '' );
+ update_option( 'gmt_offset', 0 );
+
+ parent::tearDown();
+ }
+
/**
* @return array
*/
@@ -47,4 +123,279 @@ final class WC_Data_Store_WP_Test extends WC_Unit_Test_Case {
$product->delete();
}
+
+ /**
+ * Day-precision meta date boundaries per operator, for a site in America/New_York.
+ *
+ * The bounds are half-open, so a day is matched in full and exactly once. For a range the
+ * upper bound is the midnight *after* the final day, so that the whole final day is covered.
+ *
+ * @return array
+ */
+ public function day_precision_meta_boundary_provider(): array {
+ return array(
+ 'equals' => array(
+ '2026-07-20',
+ array(
+ array(
+ 'value' => self::NY_JUL_20_MIDNIGHT,
+ 'compare' => '>=',
+ ),
+ array(
+ 'value' => self::NY_JUL_21_MIDNIGHT,
+ 'compare' => '<',
+ ),
+ ),
+ ),
+ 'greater than' => array(
+ '>2026-07-20',
+ array(
+ array(
+ 'value' => self::NY_JUL_21_MIDNIGHT,
+ 'compare' => '>=',
+ ),
+ ),
+ ),
+ 'greater than or equals' => array(
+ '>=2026-07-20',
+ array(
+ array(
+ 'value' => self::NY_JUL_20_MIDNIGHT,
+ 'compare' => '>=',
+ ),
+ ),
+ ),
+ 'less than' => array(
+ '<2026-07-20',
+ array(
+ array(
+ 'value' => self::NY_JUL_20_MIDNIGHT,
+ 'compare' => '<',
+ ),
+ ),
+ ),
+ 'less than or equals' => array(
+ '<=2026-07-20',
+ array(
+ array(
+ 'value' => self::NY_JUL_21_MIDNIGHT,
+ 'compare' => '<',
+ ),
+ ),
+ ),
+ 'range' => array(
+ '2026-07-20...2026-07-25',
+ array(
+ array(
+ 'value' => self::NY_JUL_20_MIDNIGHT,
+ 'compare' => '>=',
+ ),
+ array(
+ 'value' => self::NY_JUL_26_MIDNIGHT,
+ 'compare' => '<',
+ ),
+ ),
+ ),
+ 'explicit UTC instant' => array(
+ '2026-07-20T02:00:00Z',
+ array(
+ array(
+ 'value' => self::NY_JUL_20_MIDNIGHT,
+ 'compare' => '>=',
+ ),
+ array(
+ 'value' => self::NY_JUL_21_MIDNIGHT,
+ 'compare' => '<',
+ ),
+ ),
+ ),
+ 'explicit offset instant' => array(
+ '2026-07-20T02:00:00+05:00',
+ array(
+ array(
+ 'value' => self::NY_JUL_19_MIDNIGHT,
+ 'compare' => '>=',
+ ),
+ array(
+ 'value' => self::NY_JUL_20_MIDNIGHT,
+ 'compare' => '<',
+ ),
+ ),
+ ),
+ 'naive datetime' => array(
+ '2026-07-20 22:00:00',
+ array(
+ array(
+ 'value' => self::NY_JUL_20_MIDNIGHT,
+ 'compare' => '>=',
+ ),
+ array(
+ 'value' => self::NY_JUL_21_MIDNIGHT,
+ 'compare' => '<',
+ ),
+ ),
+ ),
+ 'single-day range' => array(
+ '2026-07-20...2026-07-20',
+ array(
+ array(
+ 'value' => self::NY_JUL_20_MIDNIGHT,
+ 'compare' => '>=',
+ ),
+ array(
+ 'value' => self::NY_JUL_21_MIDNIGHT,
+ 'compare' => '<',
+ ),
+ ),
+ ),
+ );
+ }
+
+ /**
+ * @testdox Day-precision meta date queries should anchor on the site's local midnight for every operator.
+ *
+ * @dataProvider day_precision_meta_boundary_provider
+ *
+ * @param string $query_var The date query var.
+ * @param array $expected Expected value/compare pairs, in order.
+ */
+ public function test_day_precision_meta_boundaries_use_site_timezone( string $query_var, array $expected ): void {
+ update_option( 'timezone_string', 'America/New_York' );
+
+ $result = $this->sut->parse_date_for_wp_query( $query_var, '_date_paid' );
+
+ $this->assertCount(
+ count( $expected ),
+ $result['meta_query'],
+ 'Wrong number of meta query clauses for ' . $query_var
+ );
+
+ foreach ( $expected as $index => $clause ) {
+ $this->assertSame(
+ $clause['value'],
+ $result['meta_query'][ $index ]['value'],
+ "Wrong boundary timestamp in clause {$index} for {$query_var}"
+ );
+ $this->assertSame(
+ $clause['compare'],
+ $result['meta_query'][ $index ]['compare'],
+ "Wrong comparison operator in clause {$index} for {$query_var}"
+ );
+ }
+ }
+
+ /**
+ * @testdox A day-precision date the calendar cannot represent should drop the date constraint rather than throw.
+ */
+ public function test_day_precision_meta_boundaries_survive_an_unrepresentable_date(): void {
+ update_option( 'timezone_string', 'America/New_York' );
+
+ // Resolves to the year 10026, which DateTime refuses to parse.
+ $result = $this->sut->parse_date_for_wp_query( '+8000 years', '_date_paid' );
+
+ $this->assertSame(
+ array(
+ array(
+ 'key' => '_date_paid',
+ 'value' => array( 1, 0 ),
+ 'type' => 'NUMERIC',
+ 'compare' => 'BETWEEN',
+ ),
+ ),
+ $result['meta_query'],
+ 'An unrepresentable date should constrain the query to an empty range rather than dropping the date filter'
+ );
+ }
+
+ /**
+ * @testdox Day-precision meta date queries should anchor on local midnight on sites using a manual UTC offset.
+ */
+ public function test_day_precision_meta_boundaries_use_manual_utc_offset(): void {
+ update_option( 'timezone_string', '' );
+ update_option( 'gmt_offset', -4 );
+
+ $result = $this->sut->parse_date_for_wp_query( '2026-07-20', '_date_paid' );
+
+ $this->assertSame( self::NY_JUL_20_MIDNIGHT, $result['meta_query'][0]['value'], 'Start should be local midnight, not UTC midnight' );
+ $this->assertSame( self::NY_JUL_21_MIDNIGHT, $result['meta_query'][1]['value'], 'End should be the next local midnight' );
+ }
+
+ /**
+ * Days whose local length is not 24 hours.
+ *
+ * @return array
+ */
+ public function dst_transition_provider(): array {
+ return array(
+ 'DST ends, 25-hour day' => array(
+ 'America/New_York',
+ '2026-11-01',
+ self::NY_NOV_01_MIDNIGHT,
+ self::NY_NOV_02_MIDNIGHT,
+ ),
+ 'DST starts, 23-hour day' => array(
+ 'America/New_York',
+ '2026-03-08',
+ self::NY_MAR_08_MIDNIGHT,
+ self::NY_MAR_09_MIDNIGHT,
+ ),
+ 'DST starts at midnight, 23-hour day' => array(
+ 'Asia/Beirut',
+ '2026-03-29',
+ self::BEIRUT_MAR_29_START,
+ self::BEIRUT_MAR_30_MIDNIGHT,
+ ),
+ );
+ }
+
+ /**
+ * @testdox Day-precision meta date queries should span the real length of a day across DST transitions.
+ *
+ * @dataProvider dst_transition_provider
+ *
+ * @param string $timezone Site timezone.
+ * @param string $date The queried day.
+ * @param int $expected_start Expected start-of-day timestamp.
+ * @param int $expected_end Expected next-midnight timestamp.
+ */
+ public function test_day_precision_meta_boundaries_follow_dst_transitions( string $timezone, string $date, int $expected_start, int $expected_end ): void {
+ $this->skip_if_day_has_no_dst_transition( $timezone, $date );
+
+ update_option( 'timezone_string', $timezone );
+
+ $result = $this->sut->parse_date_for_wp_query( $date, '_date_paid' );
+
+ $this->assertSame( $expected_start, $result['meta_query'][0]['value'], "Wrong start of day for {$date} in {$timezone}" );
+ $this->assertSame( $expected_end, $result['meta_query'][1]['value'], "Wrong end of day for {$date} in {$timezone}" );
+ }
+
+ /**
+ * Skip a DST case when the runtime's timezone database puts no transition on that day.
+ *
+ * PHP resolves timezones against the tz database compiled into the binary, not the one the OS
+ * ships, so a case is only meaningful on a runtime whose database still places a transition
+ * where the case expects one. The zones above were picked because their rules hold across the
+ * tzdata versions this suite runs under, but DST policy is set by governments and does change:
+ * Egypt, for one, has reinstated and repealed it repeatedly. This is the backstop that turns a
+ * future policy change into a skip rather than a failure that reads like a real regression.
+ *
+ * The expected timestamps stay hard-coded on purpose. Recomputing them through the same
+ * DateTime API the production code uses would make the assertion tautological; this guard only
+ * reads the environment, never the code under test.
+ *
+ * @param string $timezone Site timezone.
+ * @param string $date The queried day.
+ */
+ private function skip_if_day_has_no_dst_transition( string $timezone, string $date ): void {
+ $zone = new DateTimeZone( $timezone );
+ $start = new DateTime( $date . ' 00:00:00', $zone );
+ $end = new DateTime( $date . ' 00:00:00', $zone );
+ $end->modify( 'tomorrow' );
+
+ if ( DAY_IN_SECONDS === $end->getTimestamp() - $start->getTimestamp() ) {
+ $this->markTestSkipped(
+ 'Timezone database ' . timezone_version_get() . " puts no DST transition on {$date} in {$timezone}."
+ );
+ }
+ }
}
diff --git a/plugins/woocommerce/tests/php/includes/data-stores/class-wc-order-date-query-test.php b/plugins/woocommerce/tests/php/includes/data-stores/class-wc-order-date-query-test.php
new file mode 100644
index 00000000000..80d0cc00845
--- /dev/null
+++ b/plugins/woocommerce/tests/php/includes/data-stores/class-wc-order-date-query-test.php
@@ -0,0 +1,195 @@
+<?php
+/**
+ * Tests that day-precision date queries behave identically on both order storage backends.
+ *
+ * Parity is scoped to ordinary 24-hour days and to `date_paid`. HPOS is an hour out on DST
+ * transition days, tracked in #68060. `date_created` and `date_modified` take the `post_date`
+ * branch, which names the day in the site timezone rather than UTC. Both predate this suite.
+ *
+ * @package WooCommerce\Tests\DataStores
+ */
+
+declare( strict_types = 1 );
+
+use Automattic\WooCommerce\RestApi\UnitTests\Helpers\OrderHelper;
+use Automattic\WooCommerce\Utilities\OrderUtil;
+
+//phpcs:disable Squiz.Classes.ClassFileName.NoMatch, Squiz.Classes.ValidClassName.NotCamelCaps -- Legacy class name.
+
+/**
+ * Class WC_Order_Date_Query_Test.
+ *
+ * @group order-query-tests
+ */
+class WC_Order_Date_Query_Test extends WC_Unit_Test_Case {
+
+ /**
+ * Timestamp of 2026-07-20 21:00:00 -04:00, which falls on 2026-07-21 in UTC. This is the case a
+ * UTC-anchored day boundary gets wrong: the order belongs to the 20th in the site's timezone.
+ */
+ private const PAID_AT = 1784595600;
+
+ /**
+ * Timestamp of 1960-01-01 00:00:00 UTC, a date WooCommerce stores as a negative number.
+ */
+ private const PAID_BEFORE_EPOCH = -315619200;
+
+ /**
+ * The order storage state before the test.
+ *
+ * @var bool
+ */
+ private $previous_cot_state;
+
+ /**
+ * Set up test fixtures.
+ */
+ public function setUp(): void {
+ parent::setUp();
+
+ /*
+ * Toggling the authoritative order storage throws when any order is pending sync, and the
+ * HPOS tables outlive the per-test transaction, so a row left by an earlier test would make
+ * this suite fail for a reason unrelated to what it asserts.
+ */
+ add_filter( 'wc_allow_changing_orders_storage_while_sync_is_pending', '__return_true' );
+
+ $this->previous_cot_state = OrderUtil::custom_orders_table_usage_is_enabled();
+ OrderHelper::create_order_custom_table_if_not_exist();
+
+ update_option( 'timezone_string', 'America/New_York' );
+ }
+
+ /**
+ * Restore the order storage and timezone settings.
+ */
+ public function tearDown(): void {
+ OrderHelper::toggle_cot_feature_and_usage( $this->previous_cot_state );
+ remove_filter( 'wc_allow_changing_orders_storage_while_sync_is_pending', '__return_true' );
+ update_option( 'timezone_string', '' );
+
+ parent::tearDown();
+ }
+
+ /**
+ * Day-precision `date_paid` queries and whether an order paid at {@see self::PAID_AT} should match.
+ *
+ * @return array
+ */
+ public function date_paid_query_provider(): array {
+ return array(
+ 'the local day the order was paid' => array( '2026-07-20', true ),
+ 'the following local day' => array( '2026-07-21', false ),
+ 'the preceding local day' => array( '2026-07-19', false ),
+ 'on or after the local day' => array( '>=2026-07-20', true ),
+ 'strictly after the local day' => array( '>2026-07-20', false ),
+ 'on or before the local day' => array( '<=2026-07-20', true ),
+ 'strictly before the local day' => array( '<2026-07-20', false ),
+ 'a range ending on the local day' => array( '2026-07-19...2026-07-20', true ),
+ 'a range ending before the local day' => array( '2026-07-18...2026-07-19', false ),
+ 'a range starting on the local day' => array( '2026-07-20...2026-07-21', true ),
+ 'a range starting after the local day' => array( '2026-07-21...2026-07-22', false ),
+ 'a single-day range on the local day' => array( '2026-07-20...2026-07-20', true ),
+ 'an explicit UTC instant on the day' => array( '2026-07-20T02:00:00Z', true ),
+ 'a date the calendar cannot represent' => array( '+8000 years', false ),
+ );
+ }
+
+ /**
+ * @testdox An unrepresentable date should match no orders on either storage backend, including orders paid before 1970.
+ */
+ public function test_an_unrepresentable_date_matches_nothing_on_both_backends(): void {
+ $results = array();
+
+ $storage_backends = array(
+ 'posts' => false,
+ 'hpos' => true,
+ );
+
+ foreach ( $storage_backends as $storage => $use_hpos ) {
+ OrderHelper::toggle_cot_feature_and_usage( $use_hpos );
+
+ $this->assertSame(
+ $use_hpos,
+ OrderUtil::custom_orders_table_usage_is_enabled(),
+ "Could not switch order storage to {$storage}"
+ );
+
+ // A negative timestamp, which a one-sided lower bound would still match.
+ $order = new WC_Order();
+ $order->set_date_paid( self::PAID_BEFORE_EPOCH );
+ $order->save();
+
+ $results[ $storage ] = in_array(
+ $order->get_id(),
+ wc_get_orders(
+ array(
+ 'date_paid' => '+8000 years',
+ 'limit' => -1,
+ 'return' => 'ids',
+ )
+ ),
+ true
+ );
+
+ $order->delete( true );
+ }
+
+ $this->assertSame( $results['posts'], $results['hpos'], 'Post and HPOS storage disagree on an unrepresentable date' );
+ $this->assertFalse( $results['posts'], 'An unrepresentable date should match no orders' );
+ }
+
+ /**
+ * @testdox Day-precision date_paid queries should match on the local day the order was paid, and agree across order storage backends on an ordinary 24-hour day.
+ *
+ * @dataProvider date_paid_query_provider
+ *
+ * @param string $date_paid_query The date_paid query var.
+ * @param bool $should_match Whether the order is expected to match.
+ */
+ public function test_date_paid_day_queries_match_the_local_day( string $date_paid_query, bool $should_match ): void {
+ $results = array();
+
+ $storage_backends = array(
+ 'posts' => false,
+ 'hpos' => true,
+ );
+
+ foreach ( $storage_backends as $storage => $use_hpos ) {
+ OrderHelper::toggle_cot_feature_and_usage( $use_hpos );
+
+ $this->assertSame(
+ $use_hpos,
+ OrderUtil::custom_orders_table_usage_is_enabled(),
+ "Could not switch order storage to {$storage}"
+ );
+
+ $order = new WC_Order();
+ $order->set_date_paid( self::PAID_AT );
+ $order->save();
+
+ $matched_ids = wc_get_orders(
+ array(
+ 'date_paid' => $date_paid_query,
+ 'limit' => -1,
+ 'return' => 'ids',
+ )
+ );
+
+ $results[ $storage ] = in_array( $order->get_id(), $matched_ids, true );
+
+ $order->delete( true );
+ }
+
+ $this->assertSame(
+ $results['posts'],
+ $results['hpos'],
+ "Post and HPOS storage disagree on 'date_paid' => '{$date_paid_query}'"
+ );
+ $this->assertSame(
+ $should_match,
+ $results['posts'],
+ "Wrong result for 'date_paid' => '{$date_paid_query}' on an order paid at 2026-07-20 21:00 local time"
+ );
+ }
+}