Relative Date Ranges: Current Week of Prior Year

A handy feature in reporting applications is the ability to query data using relative date ranges. A relative date range is nothing more than a predefined start and end time offset some manner from the current date/time, but the specifics are hidden from the end-user behind a human readable label. For example, if you had a database full of access logs and wanted to query for a list of login failures that happened yesterday you could write:

<?php
$yesterday = sprintf("BETWEEN '%s 00:00:00' AND '%1\$s 23:59:59'",
    date("Y-m-d", strtotime("-1 day")));
$query = "SELECT username, tstamp, ip_address FROM failed_logins
    WHERE tstamp $yesterday";

The user could select "Yesterday" from a list, and the code would dynamically build the query accordingly.

Alternatively, you could write it entirely in SQL using MySQL's date and time handling functions. It looks a bit messier, but is just as effective:

SELECT ... BETWEEN
    DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00')
    AND DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59');

Recently I was asked to formulate a set of relative date range queries in SQL, and not all of the required ranges were as easy as "Yesterday." One such range was "Current Week of Prior Year". Before you think "oh that's easy!", keep in mind it has to determined entirely in SQL.

So the task here is to identify the week number in which the current date falls, and then return the start and end dates for that week of the previous year. The underlying assumptions are:

1. a week is defined as having a Thursday
2. the start of a week is Monday

The assumptions comes from the ISO-8601 definition and as a consequence week 1 of 2011 is Jan 3 - Jan 9 (Jan 1 and 2 are actually in week 52 of 2010).

For the sake of argument (and example), let's say today is Jan 23, 2012. According to the numbering scheme as understood with the above assumptions, Jan 23 falls within week 4 of 2012. This can be confirmed with WEEK('2012-01-23', 3) = 4 in MySQL. The target thus is to select the start and end dates of week 4 of the previous year; the results would be Jan 24 - Jan 30 of 2011.

Complicating this further, there was the requirement to adjust the starting day of the week (a modification of assumption 2). Continuing to use week 4 of 2011 (Jan 24 - Jan 30, Mon - Sun) as the example, if the user says the start of the week is Wednesday, then I would need to adjust the dates by 2 days giving Jan 26 - Feb 1. Thursday adjusts by 3 giving Jan 27 - Feb 2. etc.

The formula now has two parts:

1. calculate the start and end of the *true* year week
2. slide it into the "future" by whatever the start day would be

While the sliding into the future may or may not be correct, it would at least yield consistent results based on the conventional understanding of what "week 4" means.

I worked through deriving the formula step by step, starting with the Jan 23, 2012 example and comparing my results as I went along with the calendar at whatweekisit.com.

I first figured out the ability to get the start and end dates for week 4 of 2012 (Jan 23 to Jan 29), which is calculated with the following:

SELECT
    WEEK('2012-01-23', 3) AS weekNumber,
    DATE_SUB('2012-01-23', INTERVAL DAYOFWEEK('2012-01-23') - 2 DAY) AS startOfWeek,
    DATE_ADD('2012-01-23', INTERVAL 8 - DAYOFWEEK('2012-01-23') DAY) as endOfWeek;

Week 4 of 2011 is Jan 24 to Jan 30 (also confirmable by calendar). While it is true that the same date may not fall within the current week number and last year's week, I don't suspect they would be wildly different so a simple IF() that adjusts the calculation by an extra week seems sufficient.

SELECT
    IF (WEEK('2012-01-23', 3) = WEEK(DATE_SUB('2012-01-23', INTERVAL 1 YEAR), 3),
        DATE_SUB(DATE_SUB('2012-01-23', INTERVAL 1 YEAR),
            INTERVAL DAYOFWEEK(DATE_SUB('2012-01-23', INTERVAL 1 YEAR)) - 2 DAY),
        DATE_SUB(DATE_SUB(DATE_ADD('2012-01-23', INTERVAL 6 DAY), INTERVAL 1 YEAR),
            INTERVAL DAYOFWEEK(DATE_SUB(DATE_ADD('2012-01-23', INTERVAL 6 DAY),
            INTERVAL 1 YEAR)) - 2 DAY)) AS datetimeStart;

Now that the start and end of the target year week has been identified, I was able to replace the hard-coded dates with NOW() and apply the offset. Given `@weekDayStart` is 0 - 6 (Sun - Sat):

SELECT
    DATE_ADD(
        IF (WEEK(NOW(), 3) = WEEK(DATE_SUB(NOW(), INTERVAL 1 YEAR), 3),
            DATE_SUB(DATE_SUB(NOW(), INTERVAL 1 YEAR),
                INTERVAL DAYOFWEEK(DATE_SUB(NOW(), INTERVAL 1 YEAR)) - 2 DAY),
            DATE_SUB(DATE_SUB(DATE_ADD(NOW(), INTERVAL 6 DAY), INTERVAL 1 YEAR), 
                INTERVAL DAYOFWEEK(DATE_SUB(DATE_ADD(NOW(), INTERVAL 6 DAY), 
                INTERVAL 1 YEAR)) - 2 DAY)),
        INTERVAL @weekDayStart - 1 DAY) AS datetimeStart,

    DATE_ADD(
        IF (WEEK(NOW(), 3) = WEEK(DATE_SUB(NOW(), INTERVAL 1 YEAR), 3),
            DATE_SUB(DATE_SUB(NOW(), INTERVAL 1 YEAR), 
                INTERVAL DAYOFWEEK(DATE_SUB(NOW(), INTERVAL 1 YEAR)) - 2 DAY),
            DATE_SUB(DATE_SUB(DATE_ADD(NOW(), INTERVAL 6 DAY), INTERVAL 1 YEAR),
                INTERVAL DAYOFWEEK(DATE_SUB(DATE_ADD(NOW(), INTERVAL 6 DAY),
                INTERVAL 1 YEAR)) - 2 DAY)), 
        INTERVAL @weekDayStart + 5 DAY) AS datetimeEnd;

Working it through with the input of 2012-01-23 as NOW() and start day of Thursday (4) would yield:

1. **Jan 23 2012** = week 4
2. Week 4 of 2011 = 1/24-1/30
3. Offset Thu - Mon (4 - 1) is +3 which slides the window to **Jan 27 - Feb 2 2011**

It took me a good day to figure out, I cursed the requirements constantly, and even a friend tried to help out by posing the question on Stackoverflow which just ended up being a conversation between ourselves anyway. But in the end I think I came up with an ugly but workable solution. If you know of a better way, feel free to mention it in the comments section below!