If you ever happen to need fetching all birthdays that will occur in the following week, you may be tempted to use a simple solution with DOY (Day of Year):
def this_week_birthdays_query do
User
|> where([u], fragment("date_part('doy', ?) < date_part('doy', NOW()) + 7", u.birthday))
|> where([u], fragment("date_part('doy', ?) >= date_part('doy', NOW())", u.birthday))
end
This will work most of the year, but will break on either December 24th in a leap year or December 25th in a “normal” year (and will countinue to be broken until the New Year Eve 🎉).
When on December 25th, you want to see all users that have their birthdays between December 25th and Jan 01 of the next year, but with the solution above, you will instead look for users that have birthdays between 359th DOY and 366th DOY - the latter doesn’t exist and should be 1st DOY of the next year.
We need to know how many days there are in the year (basically, is it a leap year or a normal one), to do the math. The solution for postgres is the following - it is resistant for year overlap & leap years
def this_week_birthdays_query(days_in_year) do
User
|> where(
[u],
fragment(
"(to_char(?,'ddd')::int-to_char(now(),'DDD')::int+?)%? between 0 and 6",
u.birthday,
^days_in_year,
^days_in_year
)
)
end
