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!
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 |
+------------+