我如何过滤记录的开始时间/结束时间在mysql + codeigniter - How can I filter record by start time / end time in mysql + codeigniter

How can I filter record by start time / end time in mysql + codeigniter

表的结构是这样的

start_time TimeStamp null
end_time TimeStamp null

问题是，我想在开始时间和结束时间内获得所有记录，和所有具有空开始时间的值&结束时间值(表示记录不受时间限制)

  $this->db->where('start_date <=', now());
        $this->db->where('end_date >', now());

尝试了一些SQL的第一个要求，但没有运气，感谢帮助

听起来您需要的查询逻辑应该是这样的:

...
WHERE NOW() BETWEEN start_date AND end_date
    OR (start_date IS NULL AND end_date IS NULL)

要对Codeigniter的活动记录执行此操作，请尝试:

$this->db->where('NOW() BETWEEN start_date AND end_date');
$this->db->or_where('start_date IS NULL AND end_date IS NULL');

或者，您可以将整个内容放入一个语句中:

$where = 'NOW() BETWEEN start_date AND end_date OR (start_date IS NULL AND end_date IS NULL)';
$this->db->where($where);

您可能需要一个复合查询。

$within_range = array('start_date <=' => now(), 'end_date =>' => now());
$not_restricted = array('start_date IS NULL' => null, 'end_date IS NOT NULL' => null)
$this->db->where($within_range)->or_where($not_restricted);

有关关联数组和or_where语法的更多信息，请参见https://ellislab.com/codeigniter/user-guide/database/active_record.html#select

当然你可以使用原始sql查询:

$this->db->select('select ...');