Searchable date ranges

[tallberg]

I have a db with a column storing strings that represent date ranges such as (April, December) for the time of the year the flowers flower.

If I want to search for flowers that flower in May using a string comparison search the above would be missed out.

In an attempt to resolve this issue I have added two extra columns in the data base called Flower_begin and Flower_end.
Each column stores a number from 1 to 12 representing the month of the year.

The sql i have used so far to make selection between the two dates is:

Flower_begin >= 1 AND Flower_end <= 3

The problem is that is appear that all columns are selected instead of the ones in between.

Does any one have a better idea of how to make selections between dates ranges and make vague selections?

Thanks

[printf]

Show your table scheme and tell me what column contains the Months (April, December), because you really don't need those two extra columns, if you already have a column that has the Months (April, December)


printf

[tallberg]

Sorry not exactly sure what a db schema is.

The db is one long relation.

Feild containing the strings of dates is:    (Flowers_From  varchar(50))

Thanks for your reply

[fenway]

Sounds like you should be storing dates as, well, dates... and then you won't have any issues.

[tallberg]

I just create another column to experiment with the date data type. It seems to insist
on the following format year-month-day.

All i want to store is the months. Not a perticular date.

And be able to select between dates or even make vague selections.

Thank for replying again

[fenway]

First, Y-M-D is the SQL99 format, and should be everyone's format -- since it's easily sorted as a string.  Second, if you were to store proper dates, you can ignore the non-month parts by using the MONTH() function to just grab the month itself.  Then again, a digit (1-12) would work just as well, so maybe I'm confused about what's not working for you, or what you mean by "vague"... BETWEEN should work just fine.

[tallberg]

Im sorry if i have wasted your time. I have realize it is behaving properly.

Dates are ranges. I have discribed this by having a begining date column (Flower_begin) and an end date colum (Flower_end).

The following sql i assumed would retrieve records between begining date month 1 and end date month 3. Well it does, only the begining date can be grater than the end date. I  suppose this is fine as a plant might begin flowering in Nov and end in Jan.

SELECT * FROM `plantsdb` WHERE Flower_begin >= 1 AND Flower_end <= 3

What i ment by vague selection was if the begining date was say 2, 1 and 3 would also be selected. The sql above obviously doesnt fullfil this requirment. Ill try the between function.

[fenway]

I'm not sure what you mean... but you can simply add another condition that begin < start.

[tallberg]

To bring this to a close. The solution found.The beginning date is $flowersWhen and $flowersWhen2 is the end. The vagueness of the query range is 1 in either dirrection. If the date is January or December the range is explictly described or the range will go out side of 1-12.

Hope this will be of help to someone.

[sup]if($flowersWhen == 1)
{
  $FwTempCondition  = "(Flower_begin = 1 OR  Flower_begin = 2 OR Flower_begin = 12)";
}
elseif($flowersWhen == 12)
{
  $FwTempCondition  = "(Flower_begin = 1 OR  Flower_begin = 12 OR Flower_begin = 11)";
}
else
$FwTempCondition = "Flower_begin BETWEEN $flowersWhen -1 AND  $flowersWhen +1";

}

if($flowersWhen2 !=''){

if($FwTempCondition)
$FwTempCondition  .= " AND ";


//help the years go round
if($flowersWhen2 == 1)
{
  $FwTempCondition  .= "(Flower_end = 1 OR  Flower_end = 2 OR Flower_end = 12)";
}
elseif($flowersWhen2 == 12)
{
  $FwTempCondition  .= "(Flower_end = 1 OR  Flower_end = 12 OR Flower_end = 11)";
}
else
$FwTempCondition .= "Flower_end BETWEEN $flowersWhen2 -1 AND  $flowersWhen2 +1";

[/sup]

[fenway]

You could play around with modulus and some +/- games to keep it in mysql only, too....

[tallberg]

Please explain for this newbie

[artacus]

Trust me, take fenways advice and store your dates as dates. Yes you'll have to store them as dates and not months, but thats ok. And one date field for start_date and another for end_date. I have a feeling that your approach is going to start making a lot less sense to you around the beginning of the year when you have a end month that is less than the start month

I'd use the first day of every month. So May-July would become '2006-05-01' and '2006-07-01'
Now you have a db structure that is 100x more useable. You can use MONTH(start_date) or DATE_FORMAT(start_date,'%M') or WHERE NOW() BETWEEN start_date AND end_date