Sky on Tech

I had what I thought was a simple requirement, sort a set of MySQL 5 rows in order by the day of the year, regardless of the year. April 22nd before April 21st, etc... In particular I wanted to show photos from 'this day in history' going back over a hundred years! My DAYOFYEAR() code worked perfectly, until the Great Toronto fire of 1904...

You might not realize this, but 1904 was a leap-year, and that threw all the calculations off...

To calculate the day of the year in MySQL you do a:
SELECT DAYOFYEAR( "2010-04-22" ) which returns 112
SELECT DAYOFYEAR( "2009-04-22" ) which returns 112 as well

But what about 1904?
SELECT DAYOFYEAR( "1904-04-22" ) returns 113, as 1904 was a leap-year.

So when I tried and show all entries with a month and day-of-the-month equal or less than today, the 1904 dates from March 1st onward would be a day behind. Not cool!

A bit more detail... the MySQL view I defined included a calculated column as:
DAYOFYEAR( CONCAT( photoyear, "-", photomonth, "-", photoday ) ) AS doy

Then to show the most recent dozen historic photos from today and the last few days:
SELECT * FROM `historic-photos` WHERE doy <= DAYOFYEAR("2010-04-22") ORDER BY doy DESC LIMIT 12

My first thought was to continue with DAYOFYEAR but to change the calculation to calculate whether or not the photoyear was a leap year, and if photomonth >=3 then subtract 1. Ugly, and no doubt I'd forget why I did this at some point in the future.

Some Google searches didn't show any solution either, so I let the problem sit for a night ( while my Great Fire of 1904 photos were a day late... ) then realized a simpler solution.

I gave up on DAYOFYEAR() altogether and created a different, but consistent, calculation for the view to represent the dates:
(datemonth * 100 + dateday) AS doy

Note the year isn't even included in the calculation.... so "2010-04-22" is calculated the same as "2009-04-22" as is "1904-04-22" but in all three cases it returns:
4* 100 + 22 = 422

In my case I didn't really need to know how many days from January 1st the date was, I just wanted a number that I could use in the ORDER BY clause that was the same for each calendar date. The fact that there are lots of gaps between some sets of numbers (like Jan 29... 129... 130... 131... 201... 202... Feb 3rd... ) suited me fine.

I wouldn't exactly call this thinking outside the box, but taking a step back and focusing on the real requirement rather than the most obvious MySQL command.

Oh, and if you're curious, one example of this calculation is used to generate Historic Toronto photos from this day in history.