データの抽出方法について

Question

下記のようなテーブルがあります。

startからendまでを就業時間とした場合

例）
09:15:00から10:00:00の間で時間がかぶる人数を抽出したい
希望する返り値は三郎以外の３人

テーブルtimetable
|name(TEXT)|dat(DATE)|start(TIME)|end(TIME)|
|:--|:--:|--:|
|花子|2020-10-23|09:00:00|10:00:00|
|次郎|2020-10-23|09:00:00|09:45:00|
|太郎|2020-10-23|09:15:00|10:30:00|
|三郎|2020-10-23|09:00:00|09:15:00|

やってみたこと1
SELECT COUNT(*) AS cnt FROM timetable
AND DATE(dat)='2020-10-23'
AND start<='09:15:00'
AND end>='10:00:00'

返り値　2

やってみたこと2
SELECT COUNT(*) AS cnt FROM timetable
AND DATE(dat)='2020-10-23'
AND (start<='09:15:00 OR end>='10:00:00')

返り値　4

どのような考え方で抽出すればいいのでしょうか

Comments

[m.ts10806]

PHPは本件とどのように関係するのでしょうか。
あと「mysqli」とありますが、これはMySQLを利用ということで良いですか？
タグはなるべく正確につけられたほうが良いです。

[u-sukesan]

失礼しました、mysqliはタグのつけ間違いです。
phpに関しては、作業中のファイルがphpでしたのでつけてしまいました。
おっしゃる通り関係ありませんので外しました。

[yambejp]

三郎のend(TIME)である09:15:00は就業時間に含まれないのですね？

[u-sukesan]

yambejp　様

ありがとうございます
はい、含まないです。

逆もしかりです
四郎 2020-10-23 10:00:00 10:15:00
という人がいたら、start(TIME)の10:00:00も含まないです

[Orlofsky]

以前いっしょに開発したメンバーの人が何年か前に、勤怠管理システムで昼勤しかなったとこに夜勤が追加になって、日付をまたぐ出退勤が生じて質問のようなテーブル設計で大変更が必要になったと、実際はトラブルが発生すると職場に2日以上完徹になる人も発覚して、出勤も退勤も最初からDATE型を使っておいてくれたら ... と嘆いていました。

[u-sukesan]

Orlofsky　様

アドバイスありがとうございます。
今回は日付をまたがないという条件で大丈夫です。

Answer 1

日付をまたがないという前提付きで、
思いつく条件としては、

・startが09:15:00から10:00:00の間
または
・endが09:15:00から10:00:00の間
または
・startが09:15:00より前、endが10:00:00のより後

と、

・endが09:15:00より前 または startが10:00:00より後
以外

です。

Comments

[Gak]

先の回答がすっきり整理されてましたm(_ _)m

[u-sukesan]

先の回答で思い通りの結果を得ることができましたが、考え方を理解したので、大変参考になりました。
ありがとうございます。

Answer 2

select * from timetable where dat='2020-10-23' and start<'10:00:00' and end>'09:15:00';

Comments

[u-sukesan]

ありがとうございました
思い通りの結果を得られました
なぜそうなるのか、いろいろ試しながら理解したいと思います