SQLでwhere句に条件を付け足したが、エラーになる。

前提・実現したいこと

元々、以下のsql文がありました。

sql
1$sql  = "SELECT CAST(work.work_start_time AS DATE), (INTERVAL '24:00:00' - CAST(prev_work.work_end_time AS TIME) + CAST(CAST(work.work_start_time AS TIME) AS INTERVAL)) AS total ";
2$sql .= "FROM obc_work_histories AS work ";
3$sql .= "     INNER JOIN obc_work_histories as prev_work on work.mt_employee_id = prev_work.mt_employee_id AND CAST(prev_work.work_end_time AS DATE) = CAST(work.work_start_time AS DATE) - interval '1 day' ";
4$sql .= "WHERE (INTERVAL '24:00:00' - CAST(prev_work.work_end_time AS TIME) + CAST(CAST(work.work_start_time AS TIME) AS INTERVAL)) < INTERVAL '08:00:00' AND work.work_start_time <= ? AND ? <= work.work_end_time ";

このsql文に、「work_start_timeが、00：00：00以上（以外）のもの」という条件を付け足したいです。

発生している問題・エラーメッセージ

2020-02-12 14:20:42 Error: [PDOException] SQLSTATE[22007]: Invalid datetime format: 7 ERROR:  "timestamp"型の入力構文が無効です: "00:00:00"
LINE 1: ... <= work.work_end_time AND work.work_start_time > '00:00:00'
                                                             ^
Request URL: /car-maintenance/ObcWorkHistories/search?section=0&employee_number=&fr_date=2019-03-01&to_date=2019-03-31
Stack Trace:
#0 C:\xampp\htdocs\car-maintenance\lib\Cake\Model\Datasource\DboSource.php(458): PDOStatement->execute(Array)
#1 C:\xampp\htdocs\car-maintenance\lib\Cake\Model\Datasource\DboSource.php(424): DboSource->_execute('SELECT CAST(wor...', Array)
#2 C:\xampp\htdocs\car-maintenance\lib\Cake\Model\Datasource\DboSource.php(665): DboSource->execute('SELECT CAST(wor...', Array, Array)
#3 C:\xampp\htdocs\car-maintenance\lib\Cake\Model\Datasource\DboSource.php(609): DboSource->fetchAll('SELECT CAST(wor...', Array, Array)
#4 [internal function]: DboSource->query('SELECT CAST(wor...', Array, false)
#5 C:\xampp\htdocs\car-maintenance\lib\Cake\Model\Model.php(3307): call_user_func_array(Array, Array)
#6 C:\xampp\htdocs\car-maintenance\app\Model\AttendanceCheckerCondition\DailyRestTimeChecker.php(31): Model->query('SELECT CAST(wor...', Array, false)
#7 C:\xampp\htdocs\car-maintenance\app\Model\AttendanceChecker.php(87): DailyRestTimeChecker->check(137, '2019-03-01', '2019-03-31', true, Array)
#8 C:\xampp\htdocs\car-maintenance\app\Controller\ObcWorkHistoriesController.php(135): AttendanceChecker->check(137, '2019-03-01', '2019-03-31', true, true, Array)
#9 C:\xampp\htdocs\car-maintenance\app\Controller\ObcWorkHistoriesController.php(39): ObcWorkHistoriesController->searchData()
#10 [internal function]: ObcWorkHistoriesController->search()
#11 C:\xampp\htdocs\car-maintenance\lib\Cake\Controller\Controller.php(490): ReflectionMethod->invokeArgs(Object(ObcWorkHistoriesController), Array)
#12 C:\xampp\htdocs\car-maintenance\lib\Cake\Routing\Dispatcher.php(185): Controller->invokeAction(Object(CakeRequest))
#13 C:\xampp\htdocs\car-maintenance\lib\Cake\Routing\Dispatcher.php(160): Dispatcher->_invoke(Object(ObcWorkHistoriesController), Object(CakeRequest), Object(CakeResponse))
#14 C:\xampp\htdocs\car-maintenance\app\webroot\index.php(108): Dispatcher->dispatch(Object(CakeRequest), Object(CakeResponse))
#15 {main}

変更後のソースコード（変更した行のみ）

sql
1$sql .= "WHERE (INTERVAL '24:00:00' - CAST(prev_work.work_end_time AS TIME) + CAST(CAST(work.work_start_time AS TIME) AS INTERVAL)) < INTERVAL '08:00:00' AND work.work_start_time <= ? AND ? <= work.work_end_time AND work.work_start_time > '00:00:00' ";

試したこと

ANDをNOTに変えて、条件式もwork.work_start_time = '00:00:00'に変えてみたりしましたが同じくエラーが出ました。

yambejp

2020/02/12 05:31

phpとmysqlでprepare処理ですか？ CASTしまくってますがもっと精査すればきれいなSQLになるのでは？

m.ts10806

2020/02/12 05:32

PHPぽいですが、直接DBに対して試すのが先です。なので、SQL部分だけ取り出してください

kuuhaku4262

2020/02/12 05:40

精査するとは具体的にどのようなことをするのでしょうか？元のSQLは別の人が作ったものでして、初心者にはなかなか理解できないものとなっています。

kuuhaku4262

2020/02/12 05:41 編集

「SQL」部分だけ取り出すというのはどういうことなのでしょうか？

yambejp

2020/02/12 05:42

> 精査するとは具体的具体的なテーブルを提示して、どういうロジックでどういう結果がほしいのか例示するところからです

m.ts10806

2020/02/12 05:56

>「SQL」部分だけ取り出すというのはどういうことなのでしょうか？ PHPコードではなく、直にDBに対して実行するSQLのコードという意味です。提示されたコードはSQLではなくPHPで文字列を変数に詰めているだけです。

kuuhaku4262

2020/02/12 06:09

＞具体的なテーブルを提示して、どういうロジックでどういう結果がほしいのか例示するところからです作り元の人に聞いたところ、精査しなくても大丈夫とのことでした。

kuuhaku4262

2020/02/12 06:11

＞PHPコードではなく、直にDBに対して実行するSQLのコードという意味です。提示されたコードはSQLではなくPHPで文字列を変数に詰めているだけです。 $result = $wHist->query($sql, [$toDate.' 23:59:59', $frDate.' 00:00:00'], false); 実際に実行している部分は上記だと思うのですが、間違っているでしょうか？

yambejp

2020/02/12 06:12

つまり、なんかわからないけど、要望通りSQL文書いてねでも仕様は教えられないよ・・・ってことですね。すみません私の力不足でお手伝いできそうもありません

kuuhaku4262

2020/02/12 06:20

申し訳ありません。自分が理解できるほど力があれば、仕様を書くことが出来たのですが......

退会済みユーザー

2020/02/12 06:32

ここまで、テーブルの定義、示されず。

m.ts10806

2020/02/12 06:34

>実際に実行している部分は上記だと思うのですが、間違っているでしょうか？はい間違っています。提示されたのはPHPのコードです。 SQL、もとい、DBはPHPからすると外部の仕組みです。直接DBに対して実行しても想定通りの結果を得られないと当然PHPから実行しても想定の結果は得られません。ですから、直接DBに対して実行できるSQLを先に作る必要があります。その区別ができない段階ではPHP触らない方が良いです。余計に混乱しますし問題の切り分けができません。

kuuhaku4262

2020/02/12 07:16

＞ここまで、テーブルの定義、示されず。テーブルの定義はどのように確認すればいいのでしょうか？

kuuhaku4262

2020/02/12 07:17

＞ですから、直接DBに対して実行できるSQLを先に作る必要があります言わんとしていることは、わかりました。まずsql単体で作ってみます。

m.ts10806

2020/02/12 07:27

個人的には、自発的にこのプロジェクトおりたほうがいいと思います。「なんだか分からないけど動いた」ような状態で組まれたシステムで損害被るのは会社です。

行動規範の内容に同意します

回答3件

書式のエラーですけどその前に、
MySQLのSQL文で間違った書き方。修正後は28倍速くなった！［日付編］

str_to_dateを使用して、きっちり変換したものをwork_end_timeやwork_start_timeと比較するようにしましょう。

投稿2020/02/12 06:05

sazi

総合スコア25327

kuuhaku4262

2020/02/12 06:17

読んではみたのですが、初心者にはとても理解できませんでした。もしよろしかったら、初心者でもわかるよう噛み砕いて教えていただけると有難いです。

sazi

2020/02/26 08:17 編集

INTERVAL '24:00:00' - CAST(prev_work.work_end_time AS TIME) + CAST(CAST(work.work_start_time AS TIME) AS INTERVAL)) < INTERVAL '08:00:00' 上記部分を prev_work.work_end_time <=XXXX and work.work_start_time >= XXX のように左辺が演算では無く項目のみになるように組み替えるという事です。（数学で習った左辺から右辺に移動すると符号が反転したり掛け算が割り算に変わるというアレです。）そうする事で項目に対するインデックスが有るなら、インデックスが使用されて高速になります。

行動規範の内容に同意します