[RESOLVED] How to select using a date range

phpSimon

Hi all

A table includes two DATE columns: FROM and TO.

I'm listing the contents by month. For a given month I want to see all rows that exist 'across' that month. For example, if I'm listing February, all the following rows should be selected:

FROM 2010-01-10 TO 2010-05-10
FROM 2010-02-15 TO 2010-02-25
FROM 2010-02-15 TO 2010-03-05

I've tried this (the date variables are shown this way for clarity, I'm actually using date(Y-m-d) to build them):

$datefrom = '2010-02-01';
$dateto = '2010-02-28';

SELECT * FROM table WHERE type='s' AND (start >= '$datefrom' OR end <= '$dateto')

... which selects everything, not surprisingly! If do:

SELECT * FROM table WHERE type='s' AND (start >= '$datefrom' AND end <= '$dateto')

... I clearly only get rows that start and end within that month.

I'll continue working on this but if anyone has any clever statements that will point me in the right direction I'd be really grateful.

Thanks

phpSimon

Update. I THINK this works:


$getmonth=2;

SELECT * FROM table WHERE type='s' AND ($getmonth BETWEEN MONTH(start) AND MONTH(end))";

Testing!

phpSimon

Another update. That does work, but I need the entire date considered rather than month only.

I've tried this:

$datefrom='2010-02-01';
$dateto='2010-02-28';

SELECT * FROM table WHERE type='s' AND ($datefrom BETWEEN start AND end) && ($dateto BETWEEN start AND end)

... but that gives no results. I'm guessing this is some sort of syntax issue because the month-only one is really close.

phpSimon

Got this sorted now I think!

Comments welcome.


$datefrom='2010-02-01'; 
$dateto='2010-02-28'; 

SELECT * FROM table WHERE type='s' AND 
(('$datefrom' BETWEEN start AND end) || ('$dateto' BETWEEN start AND end))"

paulnaj

You could analyse this case by case (there are only 6), but more straightforward is to isolate the records you don't want ...

end < $datefrom OR $dateto < start

and then take the the negative of this is:

$datefrom <= end AND start <= $dateto

<?php
$datefrom = '2010-02-01';
$dateto = '2010-02-28';

$SQL = '
SELECT * 
FROM `table` 
WHERE `type` = "s" 
AND ("'.$datefrom.'" <= `end` AND `start` <= "'.$dateto.'")
';
?>

halojoy

phpSimon;10945332 wrote:
Got this sorted now I think!

Comments welcome.

$datefrom='2010-02-01'; 
$dateto='2010-02-28'; 

SELECT * FROM table WHERE type='s' AND 
(('$datefrom' BETWEEN start AND end) || ('$dateto' BETWEEN start AND end))"

You are close 🙂
But what if ......
$datefrom is BEFORE 'start' and $dateto is AFTER 'end'

I am really intrigued by this puzzle you have posted.
It looks very simple to begin with. But turns out to be more difficult than that.
Anyway you will solve it. I am sure.
This is fun!
🆒

phpSimon

Thank you for your feedback.

If $datefrom is BEFORE 'start' and $dateto is AFTER 'end', I don't want to see the results in that case. I only want to see items that exist in 12 sequencial months starting from the current one. However, I'd be interested to know if there's a more correct way to handle the query.

paulnaj, thanks. I'll try that, see what results I get. I don't quite understand the logic, I need to draw it out on paper! I'm also interested that your usage of backticks, single and double quotes is different from mine too; I'll do some searching to learn more about that too.

Thanks again.

phpSimon

Another update.

Changing my query to work paulnaj's way is much better!

Not only does it work, but it fixed another issue where certain items didn't show up when they should. halojoy, your comment also helped because it highlighted the wider thinking required.

I can also see paulnaj's syntax differences are because he's enclosed the statement in single quotes, where I tend to use doubles. Still don't quite know about backticks around SQL table and column names though.

Thanks again !

paulnaj

Hi Simon,

quick run over my grammar ...

backticks - surround table names, field names in mysql - especially useful if you use names that are reserved words in mysql - just takes an extra 'error' factor out. (Note tablename.fieldname becomes tablename.fieldname)

quotes - I never use double-quotes unless I'm forced to (which is almost never). Reasons - can then save double-quotes for attributes in html. Also means you can't embed variables in strings (this is an awful practice imho).

These are just my preferences - some people and a lot of books don't follow them!

Regarding the date logic ...

[Start] and [End] are start/end dates in your table records
|... ...| represents the range of dates you are considering

There are only 6 possibilities
[Start] [End] |... ...|
[Start] |... [End] ...|
[Start] |... ...| [End]
|... [Start] [End] ...|
|... [Start] ...| [End]
|... ...| [Start] [End]

I thought you (might have) wanted the ones marked *.

Actually, it's actually easier to think about which ones you don't want and then negate that ...

[Start] [End] |... ...|
|... ...| [Start] [End]

... which can be summed up by

[End] < |... ...|
OR
|... ...| < [Start]

The negative of which (and what we really want) is ..

|... ...| <= [End]
AND
[Start] <= |... ...|

Hope that makes sense - might help you to get what you actually wanted.

phpSimon

Yes, it does - and that's an excellent explanation of the grammer and logic. Thank you, again again! I have an equally complex date-and-maths question from the same table but I think I should post it in a different topic, which I'll do!