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:

<?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!

Comments

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

    ReplyDelete

Post a Comment

Popular posts from this blog

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…

Reading Unicode (UTF-8) in C

In working on scanner code for Kiwi I did a bit of reading up on Unicode. It's not really as difficult as one might think parsing UTF-8 character by character in C. In the end I opted to use ICU so I could take advantage of its character class functions instead of rolling my own, but the by-hand method I thought was still worth sharing. Functions like getc() read in a byte from an input stream. ASCII was the predominant encoding scheme and encoded characters in 7-8 bits, so reading a byte was effectively the same as reading a character. But you can only represent 255 characters using 8 bits, far too little to represent all the characters of the world's languages. The most common Unicode scheme is UTF-8, is a multi-byte encoding scheme capable of representing over 2 million characters using 4 bytes or less. The 128 characters of 7-bit ASCII encoding scheme are encoded the same, the most-significant bit is always 0. Other characters can be encoded as multiple bytes but the mo…

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…