CodeIgniter与MySQL日期范围查询异常问题求助

Hey, I see exactly what's going on here—your tournament_end_date_time field is stored as a varchar in MySQL, and comparing string dates directly is causing unexpected behavior.

Why this happens #

When you compare two strings like '31/12/2017' and '01/04/2018', MySQL uses lexicographical order (dictionary order) instead of actual date order. Since '3' (the first character of 31/12/2017) is larger than '0' (the first character of 01/04/2018), the database thinks 31/12/2017 is "later" than 01/04/2018. That's why your query returns nothing when you use >= '31/12/2017'.

Fixes (ordered by preference) #

1. Change the database field type (best long-term solution)

The cleanest fix is to convert tournament_end_date_time and tournament_start_date_time from varchar to a proper date type like DATE or DATETIME (use DATETIME if you store time values too). This lets MySQL handle date comparisons natively, and your code will work as expected.

After updating the field type, adjust your query to use the correct date format for MySQL (Y-m-d for DATE, Y-m-d H:i:s for DATETIME):

$today = date('Y-m-d');
$this->db->select('*');
$this->db->from('tbl_tournament');
$this->db->where('is_deleted', FALSE);
$this->db->where('tournament_end_date_time >=', $today);
$this->db->order_by('tournament_start_date_time');
$query = $this->db->get();
return $query->result_array();

2. Convert string dates to date types in the query (temporary workaround)

If you can't modify the database right now, use MySQL's STR_TO_DATE() function to convert your string fields to actual dates before comparing. This ensures the comparison uses date logic instead of string logic.

Here's how to adjust your CodeIgniter query:

$today = date("d/m/Y");
$this->db->select('*');
$this->db->from('tbl_tournament');
$this->db->where('is_deleted', FALSE);
// Convert both the field value and your $today string to date objects
$this->db->where("STR_TO_DATE(tournament_end_date_time, '%d/%m/%Y') >= STR_TO_DATE('{$today}', '%d/%m/%Y')");
// Don't forget to convert the order-by field too!
$this->db->order_by("STR_TO_DATE(tournament_start_date_time, '%d/%m/%Y')");
$query = $this->db->get();
return $query->result_array();

Note: This workaround will prevent MySQL from using any indexes on the tournament_end_date_time field, which could slow down queries on large datasets. Use this only as a short-term fix.

内容的提问来源于stack exchange，提问作者Pavan Webbeez