I just dealt with this problem(how to store periodic events(including birthdays)). It is a surprisingly difficult problem. After reading up on postgres interval types my latest attempt uses them to store events, where a per year event(like a birthday) is stored as "2 months 15 days". it turns out the postgres project has put quite a bit of thought into making interval types work as expected with regards to months, not an easy task when you consider how difficult it is to treat dates mathematically.
The nice part is that now February 29 "just works" the downside is the impedance between how months and days are numbered and a how an offset from the epoch(beginning of the year) is defined. January 3rd(1-3) is stored as "0 months 2 days" as when it hits, 0 months have passed and 2 days have passed.
So the specific case of February 29 hits when 1 month has passed and 28 days have passed. 3 years out of 4 this will be the same as "2 months 0 days"(3-1) but every forth year this will be(2-28). As an aside, and the specific reason I went with interval types, every event past feburary 29 works just fine with or without a leapyear, that is, the extra day in the middle does not mess up the offset to days after it.
Honestly I curse a little as I wish months and days were 0-based. At least clocks get this right, almost, 12 hour clocks are a special breed of stupid. start at 12, then go to 1 and proceed up to 11. 24 hour clocks properly start at 0. The worst part about 12 hour clocks is that it is almost correct, replace the 12 with a 0 and it every thing be the same but now it makes sense from a moduler math point of view.
You're just 1/4 of the age of everyone else born the same year. You have the same number of laps around the sun as those people, but you've definitely had fewer birthdays. It's a common joke for leap year babies.
The nice part is that now February 29 "just works" the downside is the impedance between how months and days are numbered and a how an offset from the epoch(beginning of the year) is defined. January 3rd(1-3) is stored as "0 months 2 days" as when it hits, 0 months have passed and 2 days have passed.
So the specific case of February 29 hits when 1 month has passed and 28 days have passed. 3 years out of 4 this will be the same as "2 months 0 days"(3-1) but every forth year this will be(2-28). As an aside, and the specific reason I went with interval types, every event past feburary 29 works just fine with or without a leapyear, that is, the extra day in the middle does not mess up the offset to days after it.
Honestly I curse a little as I wish months and days were 0-based. At least clocks get this right, almost, 12 hour clocks are a special breed of stupid. start at 12, then go to 1 and proceed up to 11. 24 hour clocks properly start at 0. The worst part about 12 hour clocks is that it is almost correct, replace the 12 with a 0 and it every thing be the same but now it makes sense from a moduler math point of view.
A special curse is reserved when I think about how there is no year zero. https://www.postgresql.org/docs/13/functions-datetime.html#F...