Hello there,
I need all rows from my table of MySQL database between the hours 06:00:00 AM (dStart) and 08:00:00 AM (dEnd)
I have launched this sql query without success because I don't understand the output.
mysql> SELECT
DAYOFWEEK(dStart) AS DOW,
DATE_FORMAT(dStart,'%Y-%m-%d %h:%i:%s') as dStart,
DATE_FORMAT(dEnd,'%Y-%m-%d %h:%i:%s') as dEnd,
cd
FROM
`2019tblCd`
WHERE
(
cd IN ('P')
AND DAYOFWEEK(dStart) BETWEEN 2
AND 6
AND (
DATE_FORMAT(time(dStart),'%H:%i:%s') >= '06:00:00'
AND DATE_FORMAT(time(dEnd),'%H:%i:%s') <= '08:00:00'
)
)
ORDER BY
STR_TO_DATE(dStart, '%Y-%m-%d %H:%i:%s') DESC
LIMIT 10;
The time interval ( 06:00:00 AM and 08:00:00 AM ) is incorrect, how to do resolve this ?
Thanks in advance for any help.
Wrong output
+-----+---------------------+---------------------+----+
| DOW | dStart | dEnd | cd |
+-----+---------------------+---------------------+----+
| 6 | 2019-06-07 19:15:41 | 2019-06-07 19:38:53 | P |
| 6 | 2019-06-07 18:51:28 | 2019-06-07 19:17:47 | P |
| 6 | 2019-06-07 18:29:39 | 2019-06-07 18:35:32 | P |
| 6 | 2019-06-07 18:22:27 | 2019-06-07 18:45:33 | P |
| 6 | 2019-06-07 18:09:07 | 2019-06-07 18:38:51 | P |
| 6 | 2019-06-07 18:05:31 | 2019-06-07 18:42:46 | P |
| 6 | 2019-06-07 17:48:01 | 2019-06-07 17:58:08 | P |
| 6 | 2019-06-07 17:34:26 | 2019-06-07 18:23:23 | P |
| 6 | 2019-06-07 17:26:54 | 2019-06-07 18:08:29 | P |
| 6 | 2019-06-07 17:10:28 | 2019-06-07 17:48:48 | P |
+-----+---------------------+---------------------+----+
10 rows in set
Correct output:
+-----+---------------------+---------------------+----+
| DOW | dStart | dEnd | cd |
+-----+---------------------+---------------------+----+
| 6 | 2019-06-07 07:15:41 | 2019-06-07 07:58:53 | P |
| 6 | 2019-06-07 06:51:28 | 2019-06-07 07:17:47 | P |
| 6 | 2019-06-07 06:29:39 | 2019-06-07 06:35:32 | P |
| 6 | 2019-06-07 06:22:27 | 2019-06-07 06:45:33 | P |
| 6 | 2019-06-07 06:09:07 | 2019-06-07 06:38:51 | P |
| 6 | 2019-06-07 06:05:31 | 2019-06-07 06:42:46 | P |
| 6 | 2019-06-07 06:04:01 | 2019-06-07 06:58:08 | P |
| 6 | 2019-06-07 06:03:26 | 2019-06-07 06:23:23 | P |
| 6 | 2019-06-07 06:02:54 | 2019-06-07 06:08:29 | P |
| 6 | 2019-06-07 06:01:28 | 2019-06-07 06:48:48 | P |
+-----+---------------------+---------------------+----+
10 rows in set