How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week?

Question

I am a total newb, and I couldn't find a good way to do this anywhere. I have a database table that contains statistics that are recorded at various time throughout the week. The reporting week starts on Thursday. The table contains a datestamp column (date) that stores when the data was recorded.

I need to pull the data for a given week (reporting weeks start on Thurs). I have written the following query:

   SELECT * 
FROM `table` 
WHERE 1 = CASE 
  WHEN WEEKDAY(NOW()) = 0 THEN DATEDIFF(NOW(),`date`) BETWEEN -2 AND 4
  WHEN WEEKDAY(NOW()) = 1 THEN DATEDIFF(NOW(),`date`) BETWEEN -1 AND 5
  WHEN WEEKDAY(NOW()) = 2 THEN DATEDIFF(NOW(),`date`) BETWEEN -0 AND 6
  WHEN WEEKDAY(NOW()) = 3 THEN DATEDIFF(NOW(),`date`) BETWEEN -6 AND 0
  WHEN WEEKDAY(NOW()) = 4 THEN DATEDIFF(NOW(),`date`) BETWEEN -5 AND 1
  WHEN WEEKDAY(NOW()) = 5 THEN DATEDIFF(NOW(),`date`) BETWEEN -4 AND 2
  WHEN WEEKDAY(NOW()) = 6 THEN DATEDIFF(NOW(),`date`) BETWEEN -3 AND 3
END

This appears to work on initial testing. But I am not sure it is the best way to go about it. I don't know much about MySQL performance, but there will be over a hundred thousand records to filter. Will this query be real slow due to the number of conditions checked?

The NOW() function is used when pulling the most current report- however, it some cases I will need to do reports for other weeks- so I would substitute another date into the place.

Also, doing it this way requires re-writing the query if the reporting week changes- say the starting day changes to Wednesday.

I can't use the WEEK() function because you can only start a week on Sun or Mon with it.

Any ideas to improve this query are much appreciated!

Other Notes: Currently using MariaDB 5.3.

Answer 1

Here is a query I wrote up to give you the most recent thursday and the ending wednesday

SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

Here is an example for today, 2011-09-21

mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2011-09-15 00:00:00 | 2011-09-21 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Just replace the NOW() function calls with whatever datetime you like and you will have the week starting Thursday all the time for the give datetime you choose.

Here is another example using the specific date '2011-01-01'

mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE('2011-01-01') - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE('2011-01-01') dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2010-12-30 00:00:00 | 2011-01-05 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Your query of table referencing today would resemble something like this:

SELECT * from `table`,
(SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A) M
WHERE `date` >= thu_beg
AND `date` <= wed_end;

Give it a Try !!!

UPDATE 2011-09-22 16:27 EDT

This was my proposed query for marking Thu-Wed.

SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

How about other weeks ???

• (SELECT SUBSTR('6012345',wkndx,1) does the week starting Mon ending Sun
• (SELECT SUBSTR('5601234',wkndx,1) does the week starting Tue ending Mon
• (SELECT SUBSTR('4560123',wkndx,1) does the week starting Wed ending Tue
• (SELECT SUBSTR('3456012',wkndx,1) does the week starting Thu ending Wed
• (SELECT SUBSTR('2345601',wkndx,1) does the week starting Fri ending Thu
• (SELECT SUBSTR('1234560',wkndx,1) does the week starting Sat ending Fri
• (SELECT SUBSTR('0123456',wkndx,1) does the week starting Sun ending Sat

Answer 2

Let me restate what you're asking for to make sure I'm getting it right:

You want to pull all records for the past 7 days a specified date?

It could look like:

select * from table where `date` between $date - interval 7 day and $date

It's important to note that $date is not literal mysql syntax but just an example place holder for the starting date you want. If this is for reporting I imagine the query will be ultimately generated from some script? If that's true it might be simpler in that language and then pass in the literal value as part of the constructed query.

I'm a fan of keeping queries as simple as possible so I'd leave it like that. I'll leave room for others to contribute answers to accomplish what you want in a single SQL-fu query.

Edit: After rereading your post it seems you probably are using the date type. In which case the following block in italics may be redundant. I'm leaving it in the event it's helpful to others (and since I took the time to write it :-)

You said you're using a "datestamp"? That is not technically a Mysql datatype. Is it a datetime, timestamp or date (time and year also exist but from your context I feel those are not applicable)? I ask because you may want to make it just a date column instead of the others. If this is the right choice for you really depends on the details of how the columnn is being used and the table being queried over all. If it's sole purpose is just to pull records in a date range, regardless of time, then date is definitely the way to go. For one, it only required 3 bytes instead of 4 or 8. I can elaborate on other reasons you'd want to use date if it matches your usage requirements (i.e. you don't care about the time portion). You can find details about the different types at http://dev.mysql.com/doc/refman/5.0/en/datetime.html

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

If and only if you are using date specifically you might consider the following:

Run the query with prefixed with "explain". Details on how to interpret the output and what is best can be found at http://dev.mysql.com/doc/refman/5.0/en/explain-output.html

Once you have that explain change the query to be

select * from table where date in ("N", "N+1"..."N+7")

where you enumerate all the individual dates you are interested in. I have come across situations where it is clear MySQL is not smart enough to effecitvely use a range query (i.e. between x and y) verses enumerating specific values for small sets.

Which ever the use case you'll want to make sure that column is indexed if you're making regular reporting queries based on its values.

Answer 3

Mr @Rolando obviously answers the question but I'll propose another decision which will allow you to manipulate and customize calendars between timezones and most important group by weeks which are defined in different time zones

so, let's assume that your mySQL is running on UTC configured server and you want to have a custom calendar which is 7 hours ahead and therefore your weeks should start on Saturday 7 am

CREATE TABLE `wh_blur_calendar` (
  `date` timestamp NOT NULL ,
  `y` smallint(6) DEFAULT NULL,
  `q` tinyint(4) DEFAULT NULL,
  `m` tinyint(4) DEFAULT NULL,
  `d` tinyint(4) DEFAULT NULL,
  `w` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `wh_ints` (
  `i` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into wh_ints values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

now a popular Cartesian join which should populate your table:

INSERT INTO wh_blur_calendar (date)
SELECT DATE('2010-01-01 00:00:00 ') + INTERVAL a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i DAY
FROM wh_ints a JOIN wh_ints b JOIN wh_ints c JOIN wh_ints d JOIN wh_ints e
WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) < 10245
ORDER BY 1;

Lets update the hours:

update  db_wh_repo.wh_blur_calendar set date = date_add(date, interval 7 hour);

and finally arrange your calendar's week in a custom way

UPDATE wh_blur_calendar
SET 
    y = YEAR(date),
    q = quarter(date),
    m = MONTH(date),
    d = dayofmonth(date),
    w = week(date_add((date), interval 1 day));

Believe me I spend some hours coming up with this decision but it gives you so much freedom in case you want to group your results based on a custom time-zone and week definitions.