Skip to main content

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:

$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:

    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

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:

    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.

    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),
            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):

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

        IF (WEEK(NOW(), 3) = WEEK(DATE_SUB(NOW(), INTERVAL 1 YEAR), 3),
                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!


  1. you might be right on with that "I may be evil" comment ...


Post a Comment

Popular posts from this blog

Composing Music with PHP

I’m not an expert on probability theory, artificial intelligence, and machine learning. And even my Music 201 class from years ago has been long forgotten. But if you’ll indulge me for the next 10 minutes, I think you’ll find that even just a little knowledge can yield impressive results if creatively woven together. I’d like to share with you how to teach PHP to compose music. Here’s an example: You’re looking at a melody generated by PHP. It’s not the most memorable, but it’s not unpleasant either. And surprisingly, the code to generate such sequences is rather brief. So what’s going on? The script calculates a probability map of melodic intervals and applies a Markov process to generate a new sequence. In friendlier terms, musical data is analyzed by a script to learn which intervals make up pleasing melodies. It then creates a new composition by selecting pitches based on the possibilities it’s observed. . Standing on ShouldersComposition doesn’t happen in a vacuum. Bach was f…

Creepy JavaScript Tracking

I recently began allergy shots so my new Monday morning routine includes me sitting in a doctor's office for 30 minutes (I must wait after receiving the shots and be checked by a nurse to make sure there was no reaction). With nothing else better to do while I waited last week, I started playing around with some JavaScript. This is what I came up with:
<html> <head> <title>Test</title> <script type="text/javascript"> window.onload = function () { var mX = 0,  mY = 0, sX = 0,  sY = 0, queue = [], interval = 200, recIntv = null, playIntv = null, b = document.body, de = document.documentElement, cursor = document.getElementById("cursor"), record = document.getElementById("record"), play = document.getElementById("play"); window.onmousemove = function (e) { e = e || window.event; if (e.pageX || e.pageY) { …

Geolocation Search

Services that allow users to identify nearby points of interest continue to grow in popularity. I'm sure we're all familiar with social websites that let you search for the profiles of people near a postal code, or mobile applications that use geolocation to identify Thai restaurants within walking distance. It's surprisingly simple to implement such functionality, and in this post I will discuss how to do so.

The first step is to obtain the latitude and longitude coordinates of any locations you want to make searchable. In the restaurant scenario, you'd want the latitude and longitude of each eatery. In the social website scenario, you'd want to obtain a list of postal codes with their centroid latitude and longitude.

In general, postal code-based geolocation is a bad idea; their boundaries rarely form simple polygons, the area they cover vary in size, and are subject to change based on the whims of the postal service. But many times we find ourselves stuck on a c…