I've got a table containing dates as follows (number of rows edited for
length)
mysql> select * from chat_schedule;
+-------------+----------+---------------------+
| schedule_id | model_id | timeslot |
+-------------+----------+---------------------+
| 1 | 2 | 2001-11-08 21:00:00 |
| 2 | 2 | 2001-11-08 22:00:00 |
| 3 | 5 | 2001-11-09 16:00:00 |
| 4 | 5 | 2001-11-09 17:00:00 |
| 5 | 3 | 2001-11-09 18:00:00 |
| 6 | 3 | 2001-11-09 19:00:00 |
| 7 | 2 | 2001-11-09 21:00:00 |
| 8 | 2 | 2001-11-09 22:00:00 |
| 9 | 5 | 2001-11-09 23:00:00 |
| 83 | 7 | 2001-11-26 18:00:00 |
| 85 | 7 | 2001-11-26 19:00:00 |
| 86 | 1 | 2001-11-26 20:00:00 |
| 87 | 2 | 2001-11-26 22:00:00 |
| 88 | 2 | 2001-11-26 23:00:00 |
| 89 | 8 | 2001-11-27 18:00:00 |
| 90 | 8 | 2001-11-27 19:00:00 |
| 91 | 1 | 2001-11-27 20:00:00 |
| 92 | 2 | 2001-11-27 22:00:00 |
| 93 | 2 | 2001-11-27 23:00:00 |
| 94 | 7 | 2001-11-28 18:00:00 |
| 95 | 7 | 2001-11-28 19:00:00 |
| 96 | 1 | 2001-11-28 20:00:00 |
| 97 | 2 | 2001-11-28 22:00:00 |
| 98 | 2 | 2001-11-28 23:00:00 |
| 99 | 7 | 2001-11-29 18:00:00 |
| 100 | 7 | 2001-11-29 19:00:00 |
| 101 | 1 | 2001-11-29 20:00:00 |
| 102 | 2 | 2001-11-29 22:00:00 |
| 103 | 2 | 2001-11-29 23:00:00 |
| 104 | 7 | 2001-11-30 18:00:00 |
| 107 | 2 | 2001-11-30 22:00:00 |
| 108 | 2 | 2001-11-30 23:00:00 |
| 109 | 7 | 2001-11-30 19:00:00 |
| 110 | 1 | 2001-11-30 20:00:00 |
| 111 | 7 | 2001-12-05 17:00:00 |
+-------------+----------+---------------------+
104 rows in set (0.00 sec)
When I run the query
SELECT m.name,DATE_FORMAT(s.timeslot, '%W') as dayname,
DATE_FORMAT(s.timeslot, '%d') as date, DATE_FORMAT(s.timeslot, '%H') as
hour FROM models m, chat_schedule s WHERE m.model_id=s.model_id AND
s.timeslot >= CURRENT_TIMESTAMP() ORDER BY s.timeslot
I get back the expected result -- all scheduled chats from today forward
for as many times are currently scheduled (23 rows returned). However if I run
SELECT m.name,DATE_FORMAT(s.timeslot, '%W') as dayname,
DATE_FORMAT(s.timeslot, '%d') as date, DATE_FORMAT(s.timeslot, '%H') as
hour FROM models m, chat_schedule s WHERE m.model_id=s.model_id AND
s.timeslot >= CURRENT_TIMESTAMP() AND s.timeslot <= CURRENT_TIMESTAMP()+6
ORDER BY s.timeslot
zero rows are returned. How can I go about modifying query #1 so that only
chats scheduled for today and the next 6 days are returned (7 total days
worth)? I know my problem is calculating the dates for the second AND
clause, but I'm stumped as to where since the first portion works OK.
Thanks in advance,
Alec
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <mysql-thread92291@xxxxxxxxxxxxxxx>
To unsubscribe, e-mail
<mysql-unsubscribe-itecsoft=zyberway.com@xxxxxxxxxxxxxxx>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php