skip to Main Content

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!

2

Answers


  1. 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; 
    
    Login or Signup to reply.
  2. 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 |
    +------------+
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search