Problems with the "BETWEEN" SQL Query

bluwulf

Lets say I have a table with a feild called submit_time (as seen below) and I have a web interface where users are able to (at the click of a button...well actually at the click of selections from drop down menus) view all records that were "submitted" between a certain time (i.e the user is able to specify a time range...a "start range" and a "stop range"). The ting is the user is allowed to select from HOUR , MINUTE or SECOND or ANY combination of thoze .

EXAMPLE a user is able to specify a time of ::12 TO 19:__:20 (where under score means the user did not select anything....which is valid as long as they at least select one of the options in the "start range" and at least one of the option in the "stop range")

NOTE that the time is specified as HH:MM:SS

Lets say I have this in my DB:

+------------+
| submit_time |
+------------+
| 16:03:46 |
| 16:03:59 |
| 16:04:02 |
| 16:06:09 |
| 16:06:12 |
| 16:06:30 |
| 16:06:36 |
| 16:07:22 |
| 16:07:25 |
| 16:07:55 |
| 16:09:31 |
| 16:09:37 |
| 16:09:55 |
| 16:09:59 |
| 16:10:16 |
| 16:10:20 |
| 16:10:46 |
| 16:10:56 |
| 16:10:59 |
| 16:11:09 |
| 16:11:16 |
| 16:11:19 |
| 16:11:27 |
| 16:11:29 |
| 16:15:17 |
| 16:15:19 |
| 16:15:22 |
| 16:15:26 |
| 16:16:06 |
| 16:16:09 |
| 16:16:11 |
| 16:16:14 |
| 16:16:17 |
| 16:16:29 |
| 16:16:32 |
| 16:16:35 |
| 16:16:42 |
| 16:16:45 |
| 16:16:48 |
| 16:16:51 |
| 16:17:33 |
| 16:17:36 |
| 16:17:40 |
| 16:17:43 |
| 16:17:45 |
| 16:18:48 |
| 16:21:16 |
| 16:22:52 |
| 16:22:58 |
| 16:23:12 |
| 16:24:24 |
| 16:26:29 |
| 16:26:37 |
| 16:29:43 |
| 16:32:21 |
| 16:50:26 |
| 16:54:30 |
| 16:54:59 |
| 17:41:51 |
| 18:05:12 |
| 18:05:15 |
| 18:05:40 |
| 18:12:02 |
| 18:23:36 |
| 18:23:39 |
| 18:12:10 |
| 17:44:10 |
| 17:44:16 |
| 18:19:57 |
| 10:52:17 |
| 11:05:33 |
| 11:07:16 |
+------------+

Why does this query return :

SELECT * FROM details_submitted WHERE submit_time  BETWEEN  submit_time LIKE '__:__:12' AND submit_time LIKE '18:__:59';

Whenever I execute it in MySQL 4.0.20-standard

Empty set (0.01 sec)

Suppose, also that in the same table I had the following (corresponding) dates how would I do a a query for all reccords that fall between a given submit_time and submit_date (noting that the user is able to select date jus the same way as they selected time...jus as above)

For example I want all reccords that fall within the following time range:
16::01 TO '18::00' AND between the following date: '2004--09 TO '--13'

Where submit_date is of the format YYYY-MM-DD

+-------------+
| submit_date |
+-------------+
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2004-08-05 |
| 2003-01-09 |
| 2004-08-09 |
| 2004-08-09 |
| 2004-08-10 |
| 2004-08-13 |
| 2004-08-13 |
| 2004-08-13 |
+-------------+

Roger_Ramjet

Because nothing in your time fields is an _ is the short answer.

bluwulf

I will go look into that Roger Ramjet

But lets say I force (which I really don't want to do but...) the user to select a range consisting of a COMPLETE date (i.e YYYY-MM-DD) and and a COMLETE time (i.e HH:MM:SS)

then this works :

SELECT * FROM details_submitted WHERE submit_time >= '11:07:06' AND submit_time <='18:19:57' AND submit_date >= '2004-08-09' AND submit_date <= '2004-08-13';

It returns this :

+-------------+-------------+
| submit_date | submit_time |
+-------------+-------------+
| 2004-08-09 | 17:44:10 |
| 2004-08-09 | 17:44:16 |
| 2004-08-10 | 18:19:57 |
| 2004-08-13 | 11:07:16 |
+-------------+-------------+
4 rows in set (0.00 sec)

So I guess I will work with that....

Well here is my next question...Is there a way for me to retrieve ALLL reccords from the DB that were inserted in the last week as in last SUNDAY to last SATURDAY?

NOTE NOT the last 7 days !

Take for example suppose that that today is Friday August 13,2004 then records for the last week will all have submit_dates from

Sunday August 1,2004 to Saturday August 7,2004 i.e

2004-08-01 TO 2004-08-07

Roger_Ramjet

Really can't see what you are getting at with the 'incomplete' times. What it looks like is that users would get anything submitted after 12 seconds of ANY minute in ANY hour. How would that work?

However, to extract part of a string, in this case your times, for comparison use LEFT, MID & RIGHT http://dev.mysql.com/doc/mysql/en/String_functions.html to extract the hours, minutes, seconds, year, month, or day accordingly. Then simple > and < to match against the input.

As far as your last question goes then:

$start_date
$end_date
SELECT * FROM details_submitted
WHERE submit_date > $start_date AND submit_date < $end_date

or <= and >= if appropriate

bluwulf

Originally posted by Roger Ramjet
Really can't see what you are getting at with the 'incomplete' times. What it looks like is that users would get anything submitted after 12 seconds of ANY minute in ANY hour. How would that work?

However, to extract part of a string, in this case your times, for comparison use LEFT, MID & RIGHT http://dev.mysql.com/doc/mysql/en/String_functions.html to extract the hours, minutes, seconds, year, month, or day accordingly. Then simple > and < to match against the input.

As far as your last question goes then:
$start_date
$end_date
SELECT * FROM details_submitted
WHERE submit_date > $start_date AND submit_date < $end_date
or <= and >= if appropriate [/B]

Ok...thanx for your explanation....now with regards to the last answer...how would kno what $start_date, $end_date....in other words how would calculate this DYNAMICALLY ? Because again all the user will see is a button on a web interface that says something like LAST WEEK RECCORDS...

Roger_Ramjet

You will need to use the date() function with the correct parameters to find out what day of the week it is. Use arithmetic to calculate last week's dates. You really will have to read the manual to find out how to calculate all the things this needs depending on whether it is the beginning of the month, a leap year, etc. http://uk2.php.net/date

bluwulf

Ok this is how I plan to generate weekly reports (at the press of a button on my webpage)

This is the idea

My currrent week (and all weeks) have 7 days, from Sunday to Saturday.Lets say I assign an integer value (starting from 1) to each day of the week like this:

My CURRENT Week
Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friday = 6
Saturday = 7

So lets arbitarily pick a day of the CURRENT week...say 2004-08-14 i.e a Saturday....right away (from my little "table" above) I kno that Saturday is assigned an integer value of 7 now for me to determine the date of LAST week Sunday (my start date for LAST week)

I start to count backwards (from the CURRENT day) to LAST week Sunday, I realise that LAST week Sunday is 13 days ago (i.e if we are counting from the CURRENT day of 2004-08-14 a Saturday)

If the CURRENT day is Friday I go back 12 days ago
If the CURRENT day is Thursday I go back 11 days ago
.
.
.
.
If the CURRENT day is sunday I go back 7 days ago to determine what the date of LAST Sunday is.

That date that I determine is start_date of last week and all I have to do is "add" 6 days to that date to determine what the end_date is

And thus I will have the start_date and end_date of last week regardless of what the date is of the CURRENT week. I would now be able to specify queries for evrything inserted in my DB for "LAST WEEK" (at the click of a button)

Do U think this scheme would work ?