Filtering views by date issues

This thread is resolved. Here is a description of the problem and solution.

Problem: I would like to use a custom date field filter to display posts with custom field dates in the future, but it doesn't seem to be working.

Solution: Be sure to use the comparison type "as a number" or "as an unsigned" instead of any date or datetime comparison types.

This support ticket is created 3 years, 10 months ago. There's a good chance that you are reading advice that it now obsolete.

This is the technical support forum for Toolset - a suite of plugins for developing WordPress sites without writing PHP.

Everyone can read this forum, but only Toolset clients can post in it. Toolset support works 6 days per week, 19 hours per day.

Sun	Mon	Tue	Wed	Thu	Fri	Sat
8:00 – 12:00	8:00 – 12:00	8:00 – 12:00	8:00 – 12:00	8:00 – 12:00	-	-
13:00 – 17:00	13:00 – 17:00	13:00 – 17:00	13:00 – 17:00	13:00 – 17:00	-	-

Supporter timezone: America/New_York (GMT-04:00)

Tagged: Setting up custom fields, Types plugin, Views, Views plugin

This topic contains 2 replies, has 2 voices.

Last updated by Kelly 3 years, 10 months ago.

Assisted by: Christian Cox.

Author

Posts

June 10, 2020 at 4:35 pm #1657275

Kelly

Hello.

I've a view which is outputting a post type called Race.

Right now, it is outputting the title and some custom fields; a select box (race-type), a text line (race-target) and a date and time (race-date).

These all output fine.

If a change the query to filter on the text line, that also works.

I.e. I've 2 entries in there right now, one is "road" and the other is "trail". If I use the query filter and put

"Select items with field:
Race Type is a string equal to trail"

I get the race entry set as trail. If I swap it to road, I get the road one. This means that the view is seeing both rows and able to see the custom fields.

What I want to do is only show races in the future but it's not working. My two races have dates as 06 December 2020 10:00 and 10 January 2021 09:30

I've added a query filter like this:

"Select items with field:
Race Date is a DATETIME greater than NOW()"

but it only returns the single race with the date set as 10 January 2021 09:30.

It doesn't seem to help if I used TODAY() or DATE as well.

There is another query filter applying to this:
"Select posts in Any relationship that are related to the Post where this View is shown." as this view is being displayed in the custom template of a Macrocycle post type which is related to the races with a many to many relationship. Both of these races are linked to this Macrocycle.

Any clues on how to debug or what's going on?

June 10, 2020 at 6:43 pm #1657429

Christian Cox

Hi, if you're using a Types date/time field, that information is stored in the database in a format called a Unix timestamp. This is basically an integer representing the amount of time since the beginning of date January 1 1970: https://www.unixtimestamp.com/
The correct comparison type here would be "as a number" or "as an UNSIGNED", instead of DATETIME. Greater than NOW() should be okay. Please make this change and let me know if the results are what you would expect. If not, try temporarily deactivating all other plugins and switching to a default theme like Twenty Twenty to eliminate any possible conflicts. If you're still seeing unexpected results, I will take a closer look.

June 10, 2020 at 6:45 pm #1657433

Kelly

Great! That was it.

"Select items with field:
Race Date is a number greater than NOW()" returns what I expect.

This ticket is now closed. If you're a WPML client and need related help, please open a new support ticket.