Skip Navigation

[Resolved] Views filtering on custom date does not work

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

Problem:
Views filtering on custom date does not work with event calendar custom date field

Solution:
Types stores custom date field value as Unix Timestamp but you are using the custom field of third party plugin which stores the date value as string.

To convert the date string to timestamp you should use the posts_where hook.

You can find proposed solution, in this case, with the following reply:
https://toolset.com/forums/topic/views-filtering-on-custom-date-does-not-work/#post-1082851

Relevant Documentation:
https://codex.wordpress.org/Plugin_API/Filter_Reference/posts_where

This support ticket is created 5 years, 7 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
- 10:00 – 13:00 10:00 – 13:00 10:00 – 13:00 10:00 – 13:00 10:00 – 13:00 -
- 14:00 – 18:00 14:00 – 18:00 14:00 – 18:00 14:00 – 18:00 14:00 – 18:00 -

Supporter timezone: Asia/Kolkata (GMT+05:30)

This topic contains 6 replies, has 2 voices.

Last updated by tammot 5 years, 7 months ago.

Assisted by: Minesh.

Author
Posts
#1081364

I am trying to:

Display a list of events today & future (using Events Manager: hidden link using Pro version)

Link to a page where the issue can be seen:

hidden link

I expected to see:

List of events now & future, but I see list of all events

As a views filter I used _event_start (DATETIME) and _event_start_date (DATE) (one or the other, not simultanuous)
And as the type of field I used: UNSIGNED, SIGNED, DATETIME, DATE, TIME.

Settings:

**** using event_start (DATETIME) ****
_event_start is a DATE greater than or equal TODAY() ->shows all events
_event_start is a DATE greater than or equal NOW() -> shows all events
_event_start is a DATE greater than NOW() -> shows all events
_event_start is a DATE greater than TODAY() -> shows all events

_event_start is a DATETIME greater than NOW() -> shows all events
_event_start is a DATETIME greater than TODAY() -> shows all events
_event_start is a DATETIME greater than or equal NOW() -> shows all events
_event_start is a DATETIME greater than or equal TODAY() -> shows all events

_event_start is a UNSIGNED greater than or equal TODAY() -> empty list, no results
_event_start is a UNSIGNED greater than or equal NOW()-> empty list, no results
_event_start is a UNSIGNED greater than TODAY()-> empty list, no results
_event_start is a UNSIGNED greater than NOW()-> empty list, no results

**** using event_start_date (DATE) ****
_event_start_date is a DATE greater than or equal TODAY() ->shows all events
_event_start_date is a DATE greater than or equal NOW() -> shows all events
_event_start_date is a DATE greater than NOW() -> shows all events
_event_start_date is a DATE greater than TODAY() -> shows all events

_event_start_date is a DATETIME greater than NOW() -> shows all events
_event_start_date is a DATETIME greater than TODAY() -> shows all events
_event_start_date is a DATETIME greater than or equal NOW() -> shows all events
_event_start_date is a DATETIME greater than or equal TODAY() -> shows all events

_event_start_date is a UNSIGNED greater than or equal TODAY() -> empty list, no results
_event_start_date is a UNSIGNED greater than or equal NOW()-> empty list, no results
_event_start_date is a UNSIGNED greater than TODAY()-> empty list, no results
_event_start_date is a UNSIGNED greater than NOW()-> empty list, no results

Using SIGNED yieled also no results.

I never got the result I hoped for, I only got either all results or no results.

Do you know how to proceed?

Thanks in advance.

I will have to leave the office now for several hours, will be back.

#1081371

Sorry: correct link for duplicator files:
hidden link

Please make this invisible for others.

#1081385

Minesh
Supporter

Languages: English (English )

Timezone: Asia/Kolkata (GMT+05:30)

Hello. Thank you for contacting the Toolset support.

Well - the thing is that Types stores custom date field value as Unix Timestamp but you are using the custom field of third party plugin which stores the date value as string.

You can use Today() to compare the post_date not the custom date field.
=> https://toolset.com/documentation/user-guides/filtering-views-query-by-date/

So, to achieve and convert your date field value to timestamp we need to add filter to your field. I try to use the SFTP access details but its not working.s

Could you please share working FTP/SFTP access details as well as any specific port I need to use to login to FTP.

I have set the next reply to private which means only you and I have access to it.

#1081388
#1081391

Minesh
Supporter

Languages: English (English )

Timezone: Asia/Kolkata (GMT+05:30)

Well - I get following when I try to connect to SFTP:

Error:	Authentication failed.
Error:	Critical error: Could not connect to server

I have set the next reply to private which means only you and I have access to it.

#1081393
#1081605

My apologies, FTP details are:

sftp port 23
ID: toolset_support@power-amersfoort.nl
PW: toolset_support

Please make this private

#1082851

Minesh
Supporter

Languages: English (English )

Timezone: Asia/Kolkata (GMT+05:30)

Could you please check now.

I've added following filter to your current theme's functions.php file.

add_filter( 'posts_where' , 'func_posts_where' );
function func_posts_where( $where ) {

global $wpdb;
global $WP_Views;

 	 if($WP_Views->current_view == 1474){
		  $where = str_replace("CAST(mt1.meta_value AS SIGNED)","UNIX_TIMESTAMP(mt1.meta_value)",$where);
	 }
	return $where;
}

- As you can see we converted the meta_value to UNIX_TIMESTAMP.

Also I've changed the filter from date to number on your view - please do not make any changes there:
=> hidden link

Select items with field:
_event_start_date is a number greater than TODAY()

Now I can see all future events on following page:
=> hidden link

#1083022

Thank you!

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