Find if current month falls between 2 months without a year

Databases
1

HI,
so i’m making a seasonality guide for fish eg

Cod 1st of april to 1st dec

The year is unimportant as it is breeding season etc so doesn’t change year on year.

So i need to query my season start and end time, but only the month. So far i have tried a few variations of -

"SELECT * FROM seasonality WHERE ($now >= MONTH(start_date)) AND ($now <= MONTH(finish_date)) "

$now is just the month so 12 for dec etc (will just get from server)

I am confused though as when i query i have a record that is
start - 2014-10-01
end - 2014-12-29

The above query should return it as 12 is greater or equal to 10(month) AND 12 is less than or equal to 12(month)

unless i am missing something important.
any help would be appreciated

2

ah amend to above.

the one i thought wasn’t returning was. The problem is slightly different.

Because i am trying to find the month if the season goes between years eg Nov - apr
then in numbers Nov = 10 and Apr = 4
so it fails as apr is not bigger or equal to 12 (dec).

hmm if i use full year then it would work - 20141001 and 20150401

But i don’t want to have to change the years each time as it is irrelevant to what i am doing. and next year the now date is going to be bigger than both the above (20151201)

any thoughts? should i just pick an arbitrary 2 years and just set the now year to those?

3

How are those dates stored in the database? Literally as “2014-10-01” format?

If so, 2014-10-01 > 12 > 2014-12-29 is not a true logical statement, if that’s literally what’s happening. You’d need to make $now an actual date in the same format to start with.

If I’m understanding you correctly.

Another option would be to have records that are formatted without years, or something similar.

4

The “now” date could arbitrarily be set to the same date as your records. You could also remove years from everything, and use some sort of decimal format for dates - “5.29” or something. It depends on where you need this data. You could also store them in separate fields “month” “date” - it all depends on what you need the data for (besides this one calculation), in my mind…

5

thanks for the reply must have been typing at the same time. I’ve added a bit more explaination above and my thinking.

yep the dates are stored as correct dates at the moment eg 20141204

but i only really care about the month, although im now thinking i need some form of year even if they just stay the same as a reference point.

6

[quote=“Noppy, post:5, topic:106177, full:true”]…although im now thinking i need some form of year even if they just stay the same as a reference point.
[/quote]when you’re finished thinking about it, and have made the appropriate changes to your table, please do let us know if you have any questions about it, and if you do, please do a SHOW CREATE TABLE so we can see how you’ve designed it

7

hi,
thought i had it for a minute but sadly no

the table is just ‘id’,‘fish_id’,‘start_date’,‘end_date’

So i’ll try to explain my problem a bit clearer.

I have a seasonlity table so a fish could be in season between Nov and Apr.
This crosses between years, which would be fine if every year this was updated to reflect the current year, but i hadn’t planned on doing that.

If today was 1st dec 2014

This would be fine as it is between nov 2014 and apr 2015

But next year the date will be 1st dec 2015 and the date won’t fall within those dates.

I’ve tried loads of different ways i can think to do it but i think i’ve hit the wall. I think the only way i can do this is every year update the year in the start and end columns. I can just do a script to check and update the table when the year changes.

I’ll post a script when i get it working

thanks

8

store a month start number (so nov=11) as well as a months valid number (e.g. 6, nov to april)

then you can query these easily against CURRENT_DATE

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.