CODEWITHSUNDEEP
mysqlsqlsearch
TheCarver
TheCarver

Reputation: 19723

SQL - Date Between (xxxx-06-21 and xxxx-09-21)

How is it possible to use the BETWEEN function in MySQL, to search for dates in any year but between a specific day/month to a specific day/month? Or if it's not possible using BETWEEN, how else could I accomplish it?

To be more descriptive, I am trying to add a seasonal search to my photo archive website. So if a user chose to search for "summer" photos, it would search photos taken between 21 June and 21 September, but from any year.

If Carlsberg made SQL, I think it would be :)

WHERE date BETWEEN 'xxxx-06-21' AND 'xxxx-09-21'

Many thanks

Upvotes: 2

Views: 451

Answers (7)

paxdiablo
paxdiablo

Reputation: 882068

The solution given elsewhere, WHERE date.MONTH() || date.DAY() BETWEEN '0621' AND '0921', is a good one (and it's well worth upvoting since it's fine for most MySQL databases) but I'd like to point out that it (and most queries that involve per-row functions) won't scale that well to large tables.

Granted, my experience is that MySQL is not used that often for the sizes where it would make a huge difference but, in case it is, you should also consider the following.

A trick we've used in the past is to combine extra columns with insert/update triggers so that the cost of calculation is only incurred when necessary (when the data changes), rather than on every select.

Since the vast majority of databases are read far more often than written, this amortises that cost over all selects.

For example, add a new column CHAR(4) called MMDD and whack an index on it. Then set up insert/update triggers on the table so that the date column is used to set this new one, based on the formula already provided, date.MONTH() || date.DAY().

Then, when doing your query, skip the per-row functions and instead use:

WHERE MMDD BETWEEN '0621' AND '0921'

The fact that it's indexed will keep the speed blindingly fast, at the small cost of a trigger during insert/update and an extra column.

The cost of the trigger is irrelevant since it's less than the cost of doing it for every select operation. The extra storage required for a column is a downside but, if you examine all the questions people ask about databases, the ratio of speed problems to storage problems is rather high :-)

And, though this technically "breaks" 3NF in that you duplicate data, it's a time honoured tradition to do so for performance reasons, if you know what you're doing (ie, the triggers mitigate the "damage").

Upvotes: 3

andy
andy

Reputation: 2993

Since you don't need the year, subtract it from the date. In MySQL, you can do something like this:

select * from some_table
where date_sub(date_col, interval year(date_col) year)
between '0000-06-21' and '0000-09-21';

Upvotes: 0

LiuYan 刘研
LiuYan 刘研

Reputation: 1624

the RIGHT function may help

select
    current_date,
    right(current_date,5),
    right(current_date,5) between '06-21' and '09-21' `IsTodaySummer？`,
    '09-21' between '06-21' and '09-21' `Is 09-21 Summer？`;
+--------------+-----------------------+-----------------+-------------------+
| current_date | right(current_date,5) | IsTodaySummer？ | Is 09-21 Summer？ |
+--------------+-----------------------+-----------------+-------------------+
| 2011-09-29   | 09-29                 |               0 |                 1 |
+--------------+-----------------------+-----------------+-------------------+

but as ajeal said, add another column is better to use index.

Upvotes: 2

ajreal
ajreal

Reputation: 47321

If you really need to have this format of search,
I guess is better to break the date column into two,

  • year int(4) unsigned (which is the year)
  • month_day int(4) unsigned (which is mmdd)

build a composite index on month_day, year,
the idea is make index work better

example query like

where month_day between 621 and 921 -- make use on index

another example

where year=2011 and month_day between 900 and 930 -- make use on index too

Upvotes: 1

mu is too short
mu is too short

Reputation: 434785

You could pull the dates apart with the month and day functions:

where month(date_column) between 6 and 9
  and if(month(date_column) = 6 and day(date_column) < 21, 0, 1)
  and if(month(date_column) = 9 and day(date_column) > 21, 0, 1)

The if stuff is only needed because your boundaries don't line up nicely with the months.

If you're going to be doing a lot of this sort of thing then you might be better off precomputed the month-day version of the full date so that you can index it.

Upvotes: 0

user684934
user684934

Reputation:

I'd think the easy way is to take the dates, turn them into strings, take the postfix, and run it through the BETWEEN:

WHERE date.MONTH() || date.DAY() BETWEEN '0621' AND '0921'

Upvotes: 3

HJW
HJW

Reputation: 23443

There should be an equivalent of Oracle's to_date() function in MySQL.

Upvotes: 0

Related Questions