[Resolved] Date filter query not working

[Author]

Posts

[leilaG]

We are trying to show a list of tomorrows visitors and have set the custom field filter (see attached)

But are getting No items found

We've tried lots of different settings but every filter shows No items found.

hidden link

[Nigel]

Hi there

Date fields are stored as timestamps (the number of seconds since 1970) and so refer to a point in time—measured in seconds—not to a range of time such as a day.

To capture dates that correspond to "tomorrow" you need to compare BETWEEN future_day = 1 (effectively midnight tonight) and future_day = 2 (effectively midnight tomorrow).

[leilaG]

Hi Nigel, we tried that its not working, even if we add a filter to show the whole month, no results are show.

[Nigel]

If you go to Toolset > Settings > Front-end Content you can turn on full debug mode for Views, which will launch a pop-up when you load a page with a View that you can use to check the query arguments as well as the resulting MySQL query.

Can you expand the section for wpv_filter_query and paste what it shows when you use the filters as I described?

[leilaG]

wpv_filter_query
Array
(
[post_type] => Array
(
[0] => visitor
)

[paged] => 1
[suppress_filters] =>
[ignore_sticky_posts] => 1
[posts_per_page] => 10
[post__not_in] => Array
(
[0] => 10479
)

[wpv_original_limit] => -1
[wpv_original_offset] => 0
[wpv_original_posts_per_page] => 10
[tax_query] => Array
(
[0] => Array
(
[taxonomy] => group-locations
[field] => id
[terms] => Array
(
[0] => 84
)

[operator] => IN
[include_children] => 1
)

[relation] => AND
)

[meta_query] => Array
(
[0] => Array
(
[key] => arrival-date
[value] => 1560556800,1560643200
[type] => NUMERIC
[compare] => BETWEEN
)

[relation] => AND
)

[post_status] => Array
(
[0] => publish
[1] => private
)

[meta_key] => wpcf-arrival-date
[orderby] => meta_value_num
[order] => ASC

[Nigel]

Thanks for that.

I can see why it's not working. The meta_query added for your date field contains the slug without a wpcf- prefix, i.e. has a key = "arrival-date".

I would assume this is a non-Types field, except you have an ordering clause where I can see that it does include the wpcf-prefix.

Is there anything odd about your date field to explain this?

Might there be custom field values in the database for an "arrival-date" field without the prefix (i.e. a non-Types field) and when you inserted the filter and chose the field from the drop down, there was more than one "arrival-date", one for this non-Types field and another for the Types field and you have selected the non-Types field?

That's the only explanation I can think of.

[leilaG]

The field is a Types field (see attached)

[Nigel]

Can you delete the existing filter control and re-insert it, being careful to make sure that the arrival-date field you insert comes from the expected field group and isn't some stray post meta that has an entry in the database with the arrival-date slug?

[leilaG]

It's working now, thanks, but for some reason it's showing 2 days ahead not tomorrows

So today is the 17th and it is showing the 19th with the filter.

wpv_filter_query
Array
(
[post_type] => Array
(
[0] => visitor
)

[paged] => 1
[suppress_filters] =>
[ignore_sticky_posts] => 1
[posts_per_page] => 10
[post__not_in] => Array
(
[0] => 10479
)

[wpv_original_limit] => -1
[wpv_original_offset] => 0
[wpv_original_posts_per_page] => 10
[tax_query] => Array
(
[0] => Array
(
[taxonomy] => group-locations
[field] => id
[terms] => Array
(
[0] => 84
)

[operator] => IN
[include_children] => 1
)

[relation] => AND
)

[meta_query] => Array
(
[0] => Array
(
[key] => wpcf-arrival-date
[value] => 1560816000,1560902400
[type] => NUMERIC
[compare] => BETWEEN
)

[relation] => AND
)

[Nigel]

Using a toolset such as hidden link you can convert those timestamps in the query to dates, which shows the comparison is between 2019-06-18 00:00:00 and 2019-06-19 00:00:00, so should be returning posts with an arrival date of June 18.

I'll need to do some extra testing to clarify how BETWEEN handles dates that are equal, but it would also be helpful to know which timezone your site is set to.

[leilaG]

Okay, It is set to UTC+0

[Nigel]

I've found with the BETWEEN comparison that it's actually an inclusive between (effectively using greater than or equals and less than or equals), meaning that you shouldn't need to use it after all. (It can be helpful in circumstances where your timezone is different to UTC, but yours is not.)

Hence, it should work with equals.

I just tested it and it did (see my setting in the screenshot).

But that brings us back to where we started.

It's definitely not working on your site?

I'll take a look. Which is the View in question?

[leilaG]

Okay yes I just tried it and I'm getting No items found

hidden link
hidden link

[Nigel]

I just looked at this now and it appears to be working: hidden link (screenshot).

There is one visitor post that meets the test, i.e. is arriving tomorrow, June 19, and they are displayed as expected.

[leilaG]

That's strange yesterday on the 17th it was showing that same visitor post of the 19th but not showing the visitor post 18th.

I have just made a visitor post the 20th and changed the filter to future_day = 2 to test it is working as should but the post does not display.

Otherwise I guess we will just have to change it back to future_day = 1 and wait till tomorrow to see if the 20th post shows