Determining a person’s age should be easy. The difference between the current date and their birthdate, right? Over at FantasyHockeySim.com, though, it’s become a touch more complicated than that.
Among other places, player profiles over at FHS display a player’s age. In the summer of 2023, upon the death of Maple Leafs prospect Rodion Amirov, I updated the site to stop aging deceased players. This meant adding a death_date field to our player table. When getting the player’s age we’d determine the difference between the birthdate and the date of death. If no death date was available, we’d use the current date.
Over the last year I’ve been working on another change that allows retro leagues to be played at FHS. In the case of a retro league, “today” might not be today’s date. And that makes determining player ages interesting.
SELECT IF((ISNULL(player_death_date) OR (player_death_date > IFNULL(league_date, NOW()))), TIMESTAMPDIFF(year, player_birthdate, IFNULL(league_date, NOW())), TIMESTAMPDIFF(year, player_birthdate, player_death_date)) player_age FROM player INNER JOIN league ON player.league_id = league.league_id WHERE player.player_id = :player_id
This isn’t an actual query from FHS – I’ve stripped it down to the important bits – but it shows the logic required to determine a player’s age when said player may be deceased and when “today” may be variable.
There are three variables in play, the player’s birthdate, optional death date, and the optional “today” that the league uses. These are represented by player.player_birthdate, player.player_death_date, and league.league date. Every player record includes a league_id so we can use that to join from player to league. Combined with NOW() for league’s that don’t have a league_date, it gets us everything we need.
The basic idea is that, if a player is alive, we get their age by the diff of today’s date and their birthdate, where “today” is the league date if one is available, otherwise it is today’s actual date. If the player is deceased, as mentioned above, their age is simply the diff of their date of death. As such, player_age is determined by a big IF statement.
(ISNULL(player_death_date) OR (player_death_date > IFNULL(league_date, NOW())))
We know a player is alive if they have no death date. We also know they’re alive if we’ve recorded a death date but it falls after the current league date. I can’t imagine a scenario where a player’s death date is after NOW() but this accounts for it.
TIMESTAMPDIFF(year, player_birthdate, IFNULL(league_date, NOW()))
If our player is alive, we get the difference in years between “today” and the birthdate.
TIMESTAMPDIFF(year, player_birthdate, player_death_date)
Otherwise the player is deceased and we use their death date for that math.
Writing all of this out, I can see how to simplify it quite a bit. The birthdate never changes, only the date we compare it to, so the IF statement could be re-worked to be limited to that. Something to add to my backlog, I suppose.
