如何在 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”，然后添加了想要过滤掉的日期。