Re^2: Seeing if a Time is Within a Range

Just a note of caution when implemention a "between" function. This seems obvious, but I know its bitten me a couple of times.

Be sure you use the correct comparisons for the data your working with. You have four choices of what "between" means:
(time >= start and time <= end)
(time > start and time <= end)
(time >= start and time < end)
(time > start and time < end)

Make sure you use the correct comparison for your application.

E.g
01:01:00 – event 1 happened
01:59:00 – event 2 happened
02:00:00 – event 3 happened
02:01:00 – event 4 happened
02:59:00 – event 5 happened

Ask yourself: If you have one comparison from 01:00:00 to 02:00:00 and then a second comparison from 02:00:00 to 03:00:00, where do you want event 3 to show up? Both? First? Second? Neither?

Comment on Re^2: Seeing if a Time is Within a Range

Replies are listed 'Best First'.
Re^3: Seeing if a Time is Within a Range by pg (Canon) on Oct 26, 2004 at 21:21 UTC
You are absolutely right! Just a side note, if you use SQL between operator, you have to be aware that the values specified after the BETWEEN operator are inclusive. So if you say: `select first_name, last_name, credit_limit from customer where credit_limit between 500 and 800` [download] The result set will include those ones with credit limit equal to 500 or 800. If you want to exclude those people, you have to write a query like this: `select first_name, last_name, credit_limit from customer where credit_limit > 500 and credit_limit < 800` [download]	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^3: Seeing if a Time is Within a Range
by pg (Canon) on Oct 26, 2004 at 21:21 UTC

You are absolutely right! Just a side note, if you use SQL between operator, you have to be aware that the values specified after the BETWEEN operator are inclusive. So if you say:

select first_name, last_name, credit_limit
from customer
where credit_limit between 500 and 800
[download]

The result set will include those ones with credit limit equal to 500 or 800. If you want to exclude those people, you have to write a query like this:

select first_name, last_name, credit_limit
from customer
where credit_limit > 500 and credit_limit < 800
[download]

[reply]
[d/l]
[select]