如何在 PHP CodeIgniter 中按选定日期过滤记录?

Question

我正在尝试绘制一个带有客户报告的图表，我想过滤掉由于非商业日而关闭的多天商店。我还有一个过滤器日期从和到。

if( $dateFrom != null ) :
$dateFrom .= ' 00:00:01';
$this->db->where('created >=', $dateFrom);
endif;
if( $dateTo != null ) :
$dateTo .= ' 23:59:59';
$this->db->where('created <=', $dateTo);
endif;

我正在尝试弄清楚如何添加多天的额外过滤器。所以我目前有这样的东西，但它不起作用。

$this->db->where("created BETWEEN '2020-01-01 00:00:01' AND '2020-01-25 23:59:59'"); 
$this->db->where("created BETWEEN '2020-01-26 00:00:01' AND '2020-04-05 23:59:59'"); 
$this->db->where("created BETWEEN '2020-04-06 00:00:01' AND '2020-04-25 23:59:59'");
$this->db->where("created BETWEEN '2020-04-26 00:00:01' AND '2020-06-27 23:59:59'");
$this->db->where("created BETWEEN '2020-06-28 00:00:01' AND '2020-08-29 23:59:59'");
$this->db->where("created BETWEEN '2020-08-30 00:00:01' AND '2020-12-12 23:59:59'");
$this->db->where("created BETWEEN '2020-12-13 00:00:01' AND '2020-12-19 23:59:59'");
$this->db->where("created BETWEEN '2020-12-20 00:00:01' AND '2020-12-31 23:59:59'");

所以我只是想弄清楚如何从日期和日期中过滤出设置的过滤器中的天数。

是否有解决此问题或任何解决方法？

谢谢！

Answer 1

您可以将所有特殊日期放在这样的数组中：

$exceptions = ['2020-04-26', '2020-04-27']; // And so on

然后捕获/定义您的日期范围：

$dateFrom = $some_source_of_data;
$dateTo = $some_source_of_data2;

然后：

$this->db->where("DATE(created) BETWEEN DATE('{$dateFrom}') AND DATE('{$dateTo}')"); 
$this->db->where("DATE(created) NOT IN (".implode(',', $exceptions).")"); 

Answer 2

自己弄清楚这个问题。 我添加了"NOT BETWEEN"，然后添加了我想过滤掉的日子。