MYSQL日期范围和时间范围 [英] MYSQL DATE RANGE and TIME RANGE

问题描述

计数传真在Graveyard Shift晚上10点至上午7点完成，

Counting fax completed by Graveyard Shift 10pm - 7am,

日期和时间在不同的字段中

date and time are in different field

    $stmt = $db -> prepare("SELECT count(*) FROM fax WHERE
                date BETWEEN CURDATE() and CURDATE() + INTERVAL 1 DAY 
                    and time >= '22:00:00' and time <= '7:00:00' 
                        and shift='GY' 
                            and complete=1");                
    $stmt -> execute();
    echo $GY_COMP = $stmt -> fetchColumn();

此查询的结果始终为0，但有数据.

This query always resulting to 0 but it has data.

有人可以帮助我查询吗? 在此先感谢:)

Can someone help me with my query? Thanks in advance :)

推荐答案

我认为问题出在您的时间比较中:您有效地用time >= '22:00:00' and time <= '7:00:00'

I think the problem was in your time comparison: you effectively filtered out everything with your time >= '22:00:00' and time <= '7:00:00'

希望这会有所帮助: http://sqlfiddle.com/#!2/45108/7/0

SELECT * FROM fax
WHERE date BETWEEN CURDATE() and CURDATE() + INTERVAL 1 DAY 
and
((time >= '22:00' and time <= '23:59')
or
(time >= '0:00' and time <= '7:00'))
and shift='GY' 
and complete=1

这篇关于MYSQL日期范围和时间范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！