如何在php/mysql中使用> = AND< =以12hr时间格式获取数据? [英] How to get the data using >= AND <= in a 12hr time format in php/mysql?

问题描述

我想获取从12:00:00 PM到02:00:00 PM的时间，但是当我使用 start_time> = '12:00:00'AND end_time<= '02:00:00'.到目前为止，我已经有了这段代码.
注意:我正在使用代码点火器.

I want to get the time from 12:00:00 PM to 02:00:00 PM but it doesn't work when I use start_time >= '12:00:00' AND end_time <= '02:00:00'. I have this code so far.
Note: I'm using Code Igniter.

    $start =  date("g:i:s", strtotime($this->input->post('start')));
    $end =  date("g:i:s", strtotime($this->input->post('end')));
    $date_reserve =  $this->input->post('date_reserve');

    $sql = "SELECT materialID FROM schedule WHERE date_reserve = ? AND start_time >= ? AND end_time <= ?";
    $query = $this->db->query($sql,array($date_reserve,$start,$end));

我使用 g:i:s 将其格式化为12小时格式.当我尝试回显它时，给我 12:00:00 和 02:00:00 却没有得到 AM/PM .而且，我的数据库中start_time和end_time的数据类型为 time .什么是检索数据的正确方法以及在数据库中应使用哪种数据类型.谢谢！.

I use g:i:s to format it in a 12hr time format. And when I try to echo it, gives me 12:00:00 and 02:00:00 it doesn't get the AM/PM. And also the data type of start_time and end_time in my DB is time. What is the proper way to retrieve the data and what data type should I use in my DB. Thanks!.

推荐答案

假定您在数据库中使用 DATETIME -format作为数据类型，这将对您有所帮助:

Assuming that you use the DATETIME-format as datatype in your database this should help you:

$start =  date("h:i:s", strtotime($this->input->post('start')));
$end =  date("h:i:s", strtotime($this->input->post('end')));
...
$sql = "SELECT materialID FROM schedule WHERE date_reserve = ? AND TIME(start_time) >= ? AND TIME(end_time) <= ?";

修改:因此，如果您使用 TIME-format ，则只需更改要插入的值的格式:

So if you use TIME-format you only have to change the format of the values you are inserting:

$start =  date("h:i:s", strtotime($this->input->post('start')));
$end =  date("h:i:s", strtotime($this->input->post('end')));

这篇关于如何在php/mysql中使用> = AND< =以12hr时间格式获取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！