在过去24小时内获取添加到数据库表的行

I have a create_date (btw, this column is a varchar) column in my table products I am using codeigniter, so I communicate with the table like so:

function get_recent_products()
{
    $query = $this->db->get_where('products', array('create_date' => date('y-d-m')));
    return $query->result_array();
}

Using the above function, I want to be able to only pull rows from the table that where added within the last 24 hours. The system stores the date as: 2014-02-28

Please help me out with this!

To keep the original column type for check_date use this:

ALTER TABLE `table_name`
ADD COLUMN `check_date_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00';

UPDATE TABLE `table_name`
SET `check_date_time` = `create_date` LIMIT 9999999999999999;

from there you should be able to call from the last day and you can start filling in this field to generate times going forward :)

If the date is stored in a varchar column as 2014-02-28, then you want to use the following format for the date() function:

date('Y-m-d')

Note that case is important. The format you used date('y-d-m') would give you 14-28-02.

You'll also need to pass an appropriate timestamp to the date() function. The default is the value of time().

If you want to select records for the last 24 hours as opposed to on a specific day, you will need to store the time as well. I'd recommend using a datetime column to store both the date and time in one field. This gives you the ability to do a lot of date and time calculations directly in your SQL statements.