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

jack
January 30, 2023
204 views
0 votes
2 Answers

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!

Answers

In the below code, the logic is to convert dob_month and dob_day into a date and then do the comparison using BETWEEN operator.

Now the year value used for date conversion is based on the below logic :

Use the year value the same as that of "from date". If the date is less than the "from date", then push it to the next year. Use BETWEEN operator to check if the date is within the given date range. This logic is applied because to use BETWEEN operator the date has to be greater than or equal to the "from date" keeping month and day values intact.

Note Date_add(Date_add(Makedate(some_year_value, 1), INTERVAL (dob_month)-1 month), INTERVAL (dob_day)-1 day) is repeated 3 times. It is for creating a date out of the year, month, and day values.

SET @fromdate = date('2023-09-01 00:00:00');

SET @fromyear = year(@fromdate);

SET @todate = date('2024-02-28 23:59:59');

CREATE TABLE users
  (
     id        SERIAL PRIMARY KEY,
     dob_month TINYINT,
     dob_day   TINYINT
  );

INSERT INTO users
SET dob_month = 2,
    dob_day = 1;

SELECT *
FROM   users
WHERE  CASE
         WHEN Date_add(Date_add(Makedate(@fromyear, 1),
                           INTERVAL (dob_month)-1 month),
                      INTERVAL (dob_day)-1 day) < @fromdate THEN
         Date_add(Date_add(Makedate(@fromyear + 1, 1),
                  INTERVAL (dob_month)-1 month),
         INTERVAL (dob_day)-1 day) BETWEEN @fromdate AND @todate
         ELSE Date_add(Date_add(Makedate(@fromyear, 1),
                       INTERVAL (dob_month)-1 month),
                     INTERVAL (dob_day)-1 day) BETWEEN @fromdate AND @todate
       end;

Psuedo code for understanding the crux:

SELECT *
FROM   users
WHERE  CASE
         WHEN Date(from_date_year,dob_month,dob_day) < @fromdate THEN
         Date(from_date_year,dob_month,dob_day) BETWEEN @fromdate AND @todate
         ELSE Date(from_date_year + 1,dob_month,dob_day) BETWEEN @fromdate AND @todate
       end;

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

Please signup or login to give your own answer.

Click here to cancel reply.