MySQL: Find birthdays between a date range, but ignoring the year-CodePudding

I'm trying to query for users with birthdays falling between a given date range.

The users table stores birthdays in a pair of int columns: dob_month (1 to 12) and dob_day (1 to 31). The date range I'm querying with is a pair of date-time strings, including the year.

Here's what I've got so far:

SELECT *
FROM `users`
WHERE DATE(CONCAT_WS('-', 2023, dob_month, dob_day)) BETWEEN '2023-03-01 00:00:00' AND '2023-03-31 23:59:59'

However, this doesn't work when the date range spans multiple years.

For example, 2023-12-15 00:00:00 and 2024-01-10 23:59:59.

How can I work around this? Thanks!

CodePudding user response：

You can solve this by joining to a set of rows with individual dates.

Suppose you had another table called dates which had one row per day, spanning the whole range you need.

mysql> create table dates (date date primary key);

mysql> insert into dates(date) 
 with recursive cte as (
  select '2023-01-01' as date 
  union 
  select cte.date   interval 1 day from cte where cte.date < '2025-01-01'
) 
select * from cte;
Query OK, 732 rows affected (0.01 sec)

Now it's easy to query a subset of dates:

mysql> SELECT date
FROM dates
WHERE dates.date BETWEEN '2023-12-15 00:00:00' AND '2024-01-10 23:59:59';

...
27 rows in set (0.00 sec)

We create a sample user with a dob of January 3.

mysql> create table users ( id serial primary key, dob_month tinyint, dob_day tinyint);

mysql> insert into users set dob_month = 1, dob_day = 3;

You can join your users table to that subset of dates where the month and day match.

mysql> SELECT date FROM users JOIN dates 
  ON dob_month = MONTH(date) AND dob_day = DAY(date) 
WHERE dates.date BETWEEN '2023-12-15 00:00:00' AND '2024-01-10 23:59:59';

 ------------ 
| date       |
 ------------ 
| 2024-01-03 |
 ------------

CodePudding user response：

SELECT *
FROM `users`
WHERE dob_month BETWEEN MONTH('2023-03-01 00:00:00') AND MONTH('2023-03-31 23:59:59') 
AND dob_day BETWEEN  DAY('2023-03-01 00:00:00') AND DAY('2023-03-31 23:59:59')

Note the check on the dob_day column may be superfluous if all the birthdates for a given range of months are sought.