I have a table called 'musicplays', containing a 'playdate' column containing data like this:
As you can see, it's year-month-day format in the column, which is defined as a proper MySQL Date datatype column:
So I create a publication for this table, like so:
As you can see, it's select five columns from the musicplays table; it's ordering by column 0 and 1 (ie, playdate and playtime), both descending. And it's publication ID 52.
So here is a page I created, showing how often I listen to Vivaldi:
As you can see, it's a simple access of publication ID 52, with a filter on the ARTIST column. No sort orders are specified at this point: the one specified for publication 52 itself should kick in. And it does:
As you see, the first two columns have little down-arrows against their names, because of the descending order specified for columns 0 and 1 in the publication. And, sure enough, May's play on 28th is listed and so on backwards through the month, then a play on 16th of April, then one on the 8th March, a couple in February and so on. It's all exactly ordered as the underlying table data would be when sorted descending by playdate.
And then I decide I'd like to alter the display format of that date column. Instead of 2021-05-13, I'd like 13th May 2021. I check the date settings in the WP Data Access settings:
I see at the top of the form that 'Date format - Output' is set to 'Wordpress Format', and at the bottom of the screen, that 'the plugin uses your WordPress general settings to format your date outputs'. So, I assume that by changing the WordPress date format setting, I should be able to influence the display of the WP Data Access table's data. I accordingly alter the settings there:
I choose a format that, allegedly, displays things in Day - Full Month Name - four-digit-year format. And it does:
You can certainly see that the date is now formatted differently. Unfortunately, you can also see that March, May and February dates are all mixed up together!
If you look more carefully, you can see that single digit dates are being sorted after everything else (remember: the sort order is descending), then 2s then 1s.
Click the Date of Play column to reverse the sort order:
Again, clearly we have '1's, then '2's then single figure dates.
Basically, the date column is now being sorted as though it were mere text (where we'd expect a null-x, 1-x, 2-x, 3-x sort order).
So my question is: why does changing the date display format alter the actual sort order of date data. If I order by the playdate column in ascending order, I'd expect January to sort before April; and February should sort before March... no matter that alphabetically that isn't what you'd expect.
Even more basically: Why is changing the WordPress date format affecting my date search order, given the underlying data is stored in a DATE datatype column with an order by clause on it that should be immune from date display format issues?
I've obviously now set my site back to a setting that works, so visiting the actual page in question is probably not particularly relevant, but if you wanted to see it, it's here: