[Resolved] Views filtering on custom date does not work

[Author]

Posts

[tammot]

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.

[tammot]

Sorry: correct link for duplicator files:
hidden link

Please make this invisible for others.

[Minesh]

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.

[Minesh]

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.

[tammot]

My apologies, FTP details are:

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

Please make this private

[Minesh]

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

[tammot]

Thank you!