Sky on Tech

Each photo on my photo-blog is a record in a MySQL database, but not every day of the year has a photo. I’ve been adding photos on and off since August, 2004, and each day I’d like to automatically provide links to the older photos I’ve posted on that day in the past (ie Jan 11 2010, Jan 11 2009, Jan 11 2008 and so on, but ONLY if they exist). I could have my PHP code loop through each of the possible dates and see if there is a record, but I’d rather have a single SQL command that would return just the records I was looking for. Somehow the MOD command seemed like the right place to start, but how?

One clarification, I don’t have separate year, month, and day-of-month fields in the table, which would make this exercise unnecessary, instead I do a calculation to figure out an integer value for each calendar day, and store that in the database in the displaydate field. For some reason, lost to the sands of time, August 7th, 2004 was #37.

So, once I calculate today’s integer value I’d like to find the records that match that number -365, -365-365, -365-365-365 and so on for each previous year.

Here’s some sample data:

So if we want the query to return the records on the same day of the year going back from 767, we’d consider 767, 767-365=402, 402-365=37

But because the displaydate field has gaps in the data, we can see there is no record for 402, so we want the SQL query to return just 767 and 37.

The select statement I ended up using is:
SELECT * FROM `postings`
      WHERE mod( [today’s static integer value] - postings.displaydate, 365 ) =0

Some things to notice right away:

• This doesn’t support leap-years
• This also returns today’s entry, if there is a matching record

But it works! It considers each record, subtracts the displaydate value from the static integer value for today, then sees if the remainder, when divided by 365 is zero. If so, it adds that record to the result set.

No logic required in the application itself!