bug測試記錄

start run time(sendmailtest_ya.php) : 2017-10-25 19:09:50
==sql==
SELECT * FROM APP_FP_NOTIFY_201705
WHERE MMK_ID IN ('KRE' ,'KR3')
AND MODIFY_DATE BETWEEN TO_DATE('1970-01-01 06:03:37,'YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017-10-25 19:09:50','YYYY/MM/DD HH24:MI:SS')
AND SEND_FLAG ='N'
ORDER BY MODIFY_DATE DESC

2017-10-25 19:09:50 SQL錯誤[/app_pg/test/sendmailtest_ya.php] : ORA-01756: quoted string not properly terminated

SQL指令 : SELECT * FROM APP_FP_NOTIFY_201705 WHERE MMK_ID IN ('KRE' ,'KR3') AND MODIFY_DATE BETWEEN TO_DATE('1970-01-01 06:03:37,'YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017-10-25 19:09:50','YYYY/MM/DD HH24:MI:SS') AND SEND_FLAG ='N' ORDER BY MODIFY_DATE DESC

無異常資料

9999|系統暫時無法操作，請稍後重新操作(DB)

==
問題點：程式碼裡面的時間參數錯誤

//判斷系統日前150分鐘
$now_date = date("Y-m-d H:i:s");
$brfore150mins = date("Y-m-d H:i:s" , strtotime('-150 minutes',$now_date) ); 
$query_date = date("Ym");
$query_table = 'APP_FP_NOTIFY_'.$query_date;

紅字部分被解析為1970-01-01 06:03:37
==
調整程式如下

取得時間戳記方法：
strtotime(字串[,時間戳記]);

//取得系統時間150分鐘前
date("Y-m-d H:i:s", strtotime("- 2 hour 30 minutes"));

===
測試結果為：
start run time(sendmailtest_ya.php) : 2017-10-25 19:26:21
==sql==
SELECT * FROM APP_FP_NOTIFY_201705
WHERE MMK_ID IN ('KRE' ,'KR3')
AND MODIFY_DATE BETWEEN TO_DATE('2017-10-25 17:56:21,'YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017-10-25 19:26:21','YYYY/MM/DD HH24:MI:SS')
AND SEND_FLAG ='N'
ORDER BY MODIFY_DATE DESC

2017-10-25 19:26:21 SQL錯誤[/app_pg/test/sendmailtest_ya.php] : ORA-01756: quoted string not properly terminated

SQL指令 : SELECT * FROM APP_FP_NOTIFY_201705 WHERE MMK_ID IN ('KRE' ,'KR3') AND MODIFY_DATE BETWEEN TO_DATE('2017-10-25 17:56:21,'YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017-10-25 19:26:21','YYYY/MM/DD HH24:MI:SS') AND SEND_FLAG ='N' ORDER BY MODIFY_DATE DESC

無異常資料9999|系統暫時無法操作，請稍後重新操作(DB)

因為依舊出現sql error，因此改把sql與法前後順序作調整

//抓db的異常資料欄位
$sql  = " SELECT * FROM ".$query_table;
$sql .= " WHERE MMK_ID IN ('KRE' ,'KR3')";
$sql .= " AND MODIFY_DATE BETWEEN TO_DATE('".$brfore150mins.",'YYYY/MM/DD HH24:MI:SS') AND TO_DATE('".$now_date."','YYYY/MM/DD HH24:MI:SS')";

$sql .= " AND SEND_FLAG ='N' ORDER BY MODIFY_DATE DESC";

改為

//抓db的異常資料欄位
$sql  = " SELECT * FROM ".$query_table;
$sql .= " WHERE MMK_ID IN ('KRE' ,'KR3')";
$sql .= " AND SEND_FLAG ='N' ORDER BY MODIFY_DATE DESC";

$sql .= " AND MODIFY_DATE BETWEEN TO_DATE('".$brfore150mins.",'YYYY/MM/DD HH24:MI:SS') AND TO_DATE('".$now_date."','YYYY/MM/DD HH24:MI:SS')";

結果不是順序的問題..

是因為日期格式..

因此把程式調整為：
$brfore150mins = date("Y/m/d H:i:s", strtotime("- 2 hour 30 minutes"));

測試結果：
start run time(sendmailtest_ya.php) : 2017-10-26 11:15:33
==sql==
SELECT * FROM APP_FP_NOTIFY_201705
WHERE MMK_ID IN ('KRE' ,'KR3')
AND SEND_FLAG ='N'
ORDER BY MODIFY_DATE DESC 
AND MODIFY_DATE BETWEEN TO_DATE('2017/10/26 09:45:33,'YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017-10-26 11:15:33','YYYY/MM/DD HH24:MI:SS')

2017-10-26 11:15:33 SQL錯誤[/app_pg/test/sendmailtest_ya.php] : ORA-01756: quoted string not properly terminated
SQL指令 : SELECT * FROM APP_FP_NOTIFY_201705 WHERE MMK_ID IN ('KRE' ,'KR3') AND SEND_FLAG ='N' ORDER BY MODIFY_DATE DESC AND MODIFY_DATE BETWEEN TO_DATE('2017/10/26 09:45:33,'YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017-10-26 11:15:33','YYYY/MM/DD HH24:MI:SS')

調整：$now_date = date("Y/m/d H:i:s");　後再次測試

依舊失敗..why?

目前無法運作的sql語法：
SELECT * FROM APP_FP_NOTIFY_201705 WHERE MMK_ID IN ('KRE' ,'KR3') AND SEND_FLAG ='N' AND MODIFY_DATE BETWEEN TO_DATE('2017/10/26 10:25:18,'YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2017/10/26 11:55:18','YYYY/MM/DD HH24:MI:SS')ORDER BY MODIFY_DATE DESC

可以運作的sql語法：
select * from TABLE_NAME_201705 
where COLUMN_NAME IN ('KRE' ,'KR3') 
AND MODIFY_DATE BETWEEN TO_DATE('2011/10/10 00:00:00','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2020/12/10 23:59:59','YYYY/MM/DD HH24:MI:SS');

原來是因為..第一個TO_DATE()現在日期時間的地方少了一個右側的單引號(')..

修改好之後就可以運作了：

$sql  = " SELECT * FROM ".$query_table;
$sql .= " WHERE MMK_ID IN ('KRE' ,'KR3')";
$sql .= " AND SEND_FLAG ='N'";
$sql .= " AND MODIFY_DATE BETWEEN TO_DATE('".$brfore150mins."','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('".$now_date."','YYYY/MM/DD HH24:MI:SS')";
$sql .= "ORDER BY MODIFY_DATE DESC";