`DATE_FORMAT()` translation is incorrect for most MySQL format specifiers, silently breaking `WP_Date_Query` time queries

[swissspidy]

This is something I encountered when working on wp-cli/entity-command#639 (using WP_Site_Query with WP_Date_Query). The report below is AI-generated, so please take it with a grain of salt.

---

WP_Date_Query filtering by an exact time silently returns zero rows under the SQLite driver, with no SQL error and nothing in $wpdb->last_error. The same query returns the correct rows on MySQL/MariaDB.

Digging into it, the immediate cause is narrow (a missing float cast), but it sits on top of a broader problem: 21 of the 30 entries in MYSQL_DATE_FORMAT_TO_SQLITE_STRFTIME_MAP produce wrong output, and 9 of those produce plausible-looking wrong values rather than NULL. Several map MySQL specifiers onto valid-but-unrelated SQLite specifiers — e.g. MySQL %b (abbreviated month) is mapped to SQLite %M, which is minutes.

Tested against sqlite-database-integration 3.0.0, SQLite 3.45.1, WordPress trunk.

Reproduction

// A site registered at 2014-10-21 07:30:15.
$sites = get_sites( [
    'date_query' => [ [
        'column' => 'registered',
        'year'   => 2014, 'month'  => 10, 'day'    => 21,
        'hour'   => 7,    'minute' => 30,  'second' => 15,
    ] ],
] );

	MySQL	SQLite
year + month + day	matches	matches
… + hour + minute + second	matches	0 rows

$wpdb->last_error is empty in the failing case — the query executes successfully and just silently matches nothing.

The SQL WP_Site_Query generates is:

SELECT wp_blogs.blog_id FROM wp_blogs
WHERE (
  ( YEAR( wp_blogs.registered ) = 2014
    AND MONTH( wp_blogs.registered ) = 10
    AND DAYOFMONTH( wp_blogs.registered ) = 21
    AND DATE_FORMAT( wp_blogs.registered, '%H.%i%s' ) = 7.301500 )
)

The YEAR()/MONTH()/DAYOFMONTH() parts translate fine. The DATE_FORMAT() comparison is what fails.

Defect 1 — the float cast covers only one of the four formats WP_Date_Query emits

class-wp-mysql-on-sqlite.php has an explicit workaround for MySQL's string-to-float comparison semantics:

$cast_to_float = "'%H.%i'" === $mysql_format;
if ( true === $cast_to_float ) {
    return sprintf( 'CAST(STRFTIME(%s, %s) AS FLOAT)', $format, $date );
}

But WP_Date_Query::build_time_query() builds its format string incrementally and can emit four different values, all compared against %f (a float):

// wp-includes/class-wp-date-query.php
if ( null !== $hour ) { $format .= '%H.'; } else { $format .= '0.'; }
$format .= '%i';
if ( null !== $second ) { $format .= '%s'; }
return $wpdb->prepare( "DATE_FORMAT( $column, %s ) $compare %f", $format, $time );

Format	Emitted when	Cast applied?
%H.%i	hour + minute	✅
%H.%i%s	hour + minute + second	❌
0.%i	minute only	❌
0.%i%s	minute + second	❌

So three of the four are compared as string-vs-float and never match. Notably 0.%i produces the textually-correct '0.30' and still fails, purely because SQLite won't compare '0.30' to 0.30.

Checking in_array( $mysql_format, [ "'%H.%i'", "'%H.%i%s'", "'0.%i'", "'0.%i%s'" ], true ) would cover all four.

Defect 2 — MySQL %S / %s (seconds) are mapped to SQLite %s (Unix timestamp)

'%S' => '%s',
'%s' => '%s',

MySQL %S and %s both mean seconds, 00–59. SQLite %s is seconds since 1970-01-01; SQLite's seconds-of-minute is uppercase %S.

DATE_FORMAT('2014-10-21 07:30:15', '%S')
  MySQL  => '15'
  SQLite => '1413876615'

This is what turns %H.%i%s into '07.301413876615' above. Both entries should map to '%S'.

Defect 3 — multi-specifier expansions are never re-translated

'%r' => '%h:%i:%s %A',
'%T' => '%H:%i:%s',

These expand to MySQL specifiers, but the translation is a single strtr() call, which by design never revisits text it has already substituted. So %i and %s survive into the SQLite format string, %i is not a valid SQLite specifier, and strftime() returns NULL for the whole expression.

DATE_FORMAT('2014-10-21 07:30:15', '%T')
  MySQL  => '07:30:15'
  SQLite => NULL

SQLite 3.44+ supports %T natively, so '%T' => '%T' works. %r needs '%I:%M:%S %p' (SQLite specifiers).

Full comparison

Every MySQL specifier, DATE_FORMAT('2014-10-21 07:30:15', <code>), MariaDB 10.11 vs sqlite-database-integration 3.0.0 on SQLite 3.45.1:

Silently wrong — returns a plausible value, so callers cannot detect the failure:

Code	Meaning	Mapped to	MySQL	SQLite
%b	Abbreviated month	%M (minute)	Oct	30
%M	Full month name	%F (ISO date)	October	2014-10-21
%W	Full weekday name	%l (12-hour)	Tuesday	7
%S	Seconds	%s (Unix time)	15	1413876615
%s	Seconds	%s (Unix time)	15	1413876615
%e	Day of month	%j (day of year)	21	294
%D	Day + suffix	%jS	21st	294S
%u	Week (Mon-first)	%W (Sun-first)	43	42
%v	Week (Mon-first)	%W (Sun-first)	43	42

Returns NULL — mapped to a specifier this SQLite build does not have:

Code	Meaning	Mapped to	MySQL	SQLite
%a	Abbreviated weekday	%D	Tue	NULL
%c	Month, no padding	%n	10	NULL
%h	Hour (12)	%h	07	NULL
%I	Hour (12)	%h	07	NULL
%j	Day of year	%z	294	NULL
%k	Hour (24), no padding	%G	7	NULL
%l	Hour (12), no padding	%g	7	NULL
%p	AM/PM	%A	AM	NULL
%r	12-hour time	%h:%i:%s %A	07:30:15 AM	NULL
%T	24-hour time	%H:%i:%s	07:30:15	NULL
%x	ISO year	%o	2014	NULL
%y	2-digit year	%y	14	NULL

Correct: %d, %H, %i, %m, %U, %V, %w, %X, %Y.

Suggested direction

SQLite 3.44 (Nov 2023) added a batch of specifiers that resolve most of these directly:

MySQL	Currently	Native SQLite equivalent
%e	%j	%e
%h, %I	%h	%I
%j	%z	%j
%p	%A	%p
%S, %s	%s	%S
%T	%H:%i:%s	%T
%r	%h:%i:%s %A	%I:%M:%S %p

Two caveats on that list: SQLite's %k and %l are space-padded (' 7') where MySQL's are unpadded ('7'), so those need trimming rather than a straight mapping. And %e/%I/%p/%T require SQLite ≥ 3.44 — worth confirming against the project's minimum supported version.

The remainder have no native equivalent and would need a UDF: %a, %b, %M, %W (locale-independent name lookups), %D (ordinal suffix), %c and %y (trimming), and %x/%u/%v (ISO week/year — SQLite's %G/%V cover these but returned NULL on 3.45.1 here, so they look like 3.46+). The driver already registers UDFs via WP_SQLite_PDO_User_Defined_Functions, so the mechanism is in place.

Separately, it may be worth having an unmappable specifier throw rather than silently emit a wrong value — the existing Could not translate a DATE_FORMAT() format exception never fires for these cases because strtr() always returns a non-empty string, so the if ( ! $format ) guard cannot catch them.