Hi,
I’m trying to add a (smart)filterable date column to woo commerce for “Date Completed”.

The data is stored in the _date_completed custom field – but its stored as a unix timestamp. I know AC likes ymd format.

I’ve made a custom field plugin and it shows, but the smart filter doent work. Always no results.

Do you have – or can you supply – code example for using the between smart filter?

I tried this code:

<?php
namespace net1on1_name\SmartFiltering;
class order_completed extends \ACP\Search\Comparison {
	public function __construct() {
		$operators = new \ACP\Search\Operators( [
			\ACP\Search\Operators::EQ,
			\ACP\Search\Operators::BETWEEN,
		] );
		$value = \ACP\Search\Value::DATE;
		parent::__construct( $operators, $value );
	}

	protected function create_query_bindings( $operator, \ACP\Search\Value $value ) {
		$binding = new \ACP\Search\Query\Bindings\Post();
		if (strtolower($operator) == "between") {
			$binding->meta_query( [
				'key'     => '_date_completed',
				'value'   => $value->get_value(),
				'compare' => 'BETWEEN',
				'type'    => 'DATE'
			] );
		} else {
			$binding->meta_query( [
				'key'     => '_date_completed',
				'value'   => $value->get_value(),
				'compare' => '=',
				'type'    => 'DATE'
			] );
		}
		return $binding;
	}
}

And then this

<?php
namespace net1on1_name\SmartFiltering;
class order_completed extends \ACP\Search\Comparison {
	public function __construct() {
		$operators = new \ACP\Search\Operators( [
			\ACP\Search\Operators::EQ,
			\ACP\Search\Operators::BETWEEN,
		] );
		$value = \ACP\Search\Value::DATE;
		parent::__construct( $operators, $value );
	}

	protected function create_query_bindings( $operator, \ACP\Search\Value $value ) {
		$binding = new \ACP\Search\Query\Bindings\Post();
		global $wpdb;
		$binding->join( " INNER JOIN {$wpdb->postmeta} AS my_postmeta ON {$wpdb->posts}.ID = my_postmeta.post_id" );
		if (strtolower($operator) == "between") {
			$v = $value->get_value();
			$d1 = $v[0];
			$d2 = $v[1];
			$binding->where( $wpdb->prepare( " AND my_postmeta.meta_key = '_date_completed' AND left(FROM_UNIXTIME(my_postmeta.meta_value),10) >=  %s  and  left(FROM_UNIXTIME(my_postmeta.meta_value),10) <= %s ", $d1, $d2 ) );
		} else {
			$binding->where( $wpdb->prepare( " AND my_postmeta.meta_key = '_date_completed' AND left(FROM_UNIXTIME(my_postmeta.meta_value),10) = %s  ", $value->get_value() ) );
		}
		return $binding;
	}
}

But both still give no results.

I checked the SQL for the latter manually – and it works in phpmyadmin…

Any clues what im doing wrong?