How to group by week specifying end of week day?

tags:

mysql
sql

views:

answers:

+2 Q:

How to group by week specifying end of week day?

I need to run a report grouped by week. This could be done by using group by week(date) but the client wants to set the day of the week that marks the end of week. So it can be Tuesday, Wednesday etc. How can I work this into a group by query?

The datetime column type is unix timestamp.

+2 A:

The WEEK() function takes an optional second parameter to specify the start of the week:

This function returns the week number for date. The two-argument form of WEEK() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53.

However, it can only be set to Sunday or Monday.

UPDATE: Further to the comments below, you may want to consider adding a new column to your table to act as a grouping field, based on WEEK(DATE_ADD(date INTERVAL x DAY)), as suggested in the comments. You may want to create triggers to automatically generate this values whenever the date field is updated, and when new rows are inserted. You would then be able to create a usable index on this new field as required.

Daniel Vassallo 2010-09-21 04:34:23

I already knew this, but how to work in Tuesday?

Pentium10 2010-09-21 04:37:09

Subtract 2 days from the date so you can treat it like a week starting on Sunday.

dan04 2010-09-21 04:42:23

Sounds like a good idea. Thanks.

Pentium10 2010-09-21 04:45:00

If I have an index on the date column, will be used after I substract 2 days? I have a table with 100 million records.

Pentium10 2010-09-21 04:51:09

@Pentium10: No, the use of the WEEK function ensures the index can't be used.

OMG Ponies 2010-09-21 04:53:34

@OMG Ponies Any alternatives so the index to be used?

Pentium10 2010-09-21 04:57:10

@Pentium10: Will the client be changing the first day of the week frequently? (ie. Will it be set once on installation of the system, or will the client be changing it for compiling different reports)?

Daniel Vassallo 2010-09-21 05:00:24

Will set once on the installation of the system.

Pentium10 2010-09-21 06:32:55

@Pentium10: Updated my answer :)

Daniel Vassallo 2010-09-21 06:38:18

@OMG Ponies, are you sure regarding the WEEK()? I tried `describe select` of a query with `where week(date) = const` and it told me 'Using where; Using index'

Unreason 2010-09-21 08:01:09

@Unreason: Look at the key field in the EXPLAIN output--is it listing the name of the index associated with the `date` field? Remember that MySQL only uses one index per SELECT statement.

OMG Ponies 2010-09-21 17:09:25

If uses only one index per SELECT stmt, how can I improve a query where there are multiple filter fields and possible group bys?

Pentium10 2010-09-21 18:14:15

@Pentium10: In some cases a single index is enough. Imagine you have a users table and you want to filter by `surname` and by `gender`. An index on surname alone would be enough for the database to filter down the records to just those few that have the same `surname`. Then it will simply run through all this small subset and filter again on the `gender`... On the other hand, for more complex situations, you could use a composite index, using more than one field. This can become tricky however, because if you have a composite index on `(surname, dob)` a filter on `dob` only won't use the index

Daniel Vassallo 2010-09-21 18:18:12

Well these are much more than that. I need to compute weekly reports and daily tallies for each week, on tables having 10 millions of records.

Pentium10 2010-09-21 19:02:14

Can I execute different subqueries that will make use of indexes?

Pentium10 2010-09-21 20:29:37

@Pentium10: Can you give an example? ... Or maybe you'd want to post another SO question? There are many experts on the topic, and a new question will get much more attention.

Daniel Vassallo 2010-09-21 20:34:36

ansaurus

tags:

views:

answers:

How to group by week specifying end of week day?

related questions