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
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.Psuedo code for understanding the crux:
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.Now it’s easy to query a subset of dates:
We create a sample user with a dob of January 3.
You can join your users table to that subset of dates where the month and day match.