Hi r937 Thank you for the reply.
look, if i’m going to keep writing your queries for you, i’m going to want a share of your developer fees
2 Likes
that was a joke! i guess i shoulda used a winking smiley…
If we look away from the wrong placement of the condition and lack of quotation around the dates, why do you not recommend using BETWEEN on date ranges?
Since the column type is Datetime there should be no difference between using BETWEEN and your suggestion.
[quote=“TheRedDevil, post:24, topic:205779, full:true”]Since the column type is Datetime there should be no difference between using BETWEEN and your suggestion.
[/quote]… and yet, there is a big difference – an entire day’s worth of transactions
That is semantics, your reply could be read as if you should not use BETWEEN for this kind of a query.
There is no difference between the two options when you use the same date ranges.
I.e.
start_time BETWEEN '2015-10-09' AND '2015-10-26'
and
start_time>='2015-10-09' AND start_time<'2015-10-26'
Will give you the same result set.
I think you’re not seeing the inclusive vs exclusive difference of “between” and “or equal to”
[quote=“TheRedDevil, post:26, topic:205779, full:true”]Will give you the same result set.
[/quote]
with DATE columns, sure
but ~not~ with DATETIME columns containing datetime values
example: boss wants report for all transaction for november 5 and 6… which of these is correct?
[code]
00 03 06 09 12 15 18 21 00 03 06 09 12 15 18 21 00 03
| <------- 2015-Nov-05 -------> | <------- 2015-Nov-06 -------> | <----
| <------- query 1 -------> |
| <------- query 2 -------> |
| <------- query 3 ------------------------> |
| <------- query 4 ---------------------------------------> |
query 1: BETWEEN ‘2015-11-05’ AND ‘2015-11-06’
query 2: BETWEEN ‘2015-11-05 00:00’ AND ‘2015-11-06 00:00’
query 3: BETWEEN ‘2015-11-05 00:00’ AND ‘2015-11-06 12:00’
query 4: >= ‘2015-11-05’ AND < ‘2015-11-07’[/code]
notice “less than midnight of the day ~after~ the desired range”
not the same as your BETWEEN at all
OK, I was confused and mistaken. BETWEEN doesn’t mean what I assumed it did. 
A trip to the documentation was very helpful
https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_between
expr BETWEEN min AND max
If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. …
i think the point you are still missing is that
BETWEEN '2015-11-05' AND '2015-11-06' effectively ~excludes~ all transactions on the 6th
keep working on this until you see why
I think we are talking around each other.
The difference you show would have been less, if you used the same end date on both queries. That was my whole point.
On Datetime you are correct that there is a small difference when you only apply the date, where it will also pull any records for the start of the day.
BETWEEN '2015-11-05' AND '2015-11-06'
Will pull records from and including: ‘2015-11-05 00:00:00’ to ‘2015-11-06 00:00:00’
column>='2015-11-05' AND column<'2015-11-06'
Will pull records from and including ‘2015-11-05 00:00:00’ to ‘2015-11-05 23:59:59’
I was actually not aware that BETWEEN also pulled midnight in these cases, as I always use Datetime format on those columns, so thanks for pointing this out!
[quote=“TheRedDevil, post:31, topic:205779, full:true”]I think we are talking around each other.[/quote]not really…
all along, you haven’t been pulling the transactions for the last day of the desired range
go back and look at post #12, and notice that i ~do~ use a different end date
the 26th (or the 6th, in my later examples) WAS SUPPOSED TO BE INCLUDED
in post #12, i made two corrections – one was to include that last day, and the “less than” correction was to EXclude midnight on the 27th
Not sure, if it is really necessary to keep this going. As I said, we have talked around each other, you believe I said it was wrong to use 27th, while I never said that even once.
My point was that if you had changed the date on the BETWEEN clause as well to the same date, being 27th, 25th or any date at all it would have returned the same. This was of course wrong, since I was unaware it would also include any records that happened exactly at midnight at the last date i.e. 00:00:00 on a Datatime column.
hey, whaddya know, i agree with you on this
Hi r937,
If it is okay for you to ask this because I am curious to know how does the mysql execute your query .
does it execute the cross join first or the inner join ?.
Thank you in advance.
[quote=“jemz, post:35, topic:205779, full:true”]…does it execute the cross join first or the inner join ?.
[/quote]
yes, cross join first, then left outer join
you know how to run an EXPLAIN, right?
yes.then after cross join it compares to the left join per row ?
that’s how all joins work
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.