How to group by week no and get start date and end date for the week number in Sqlite?

Question

I want to group the result by week no and get the start date and end date for the week. I had not idea on how to do this sqlite. Could somebody help me on this.

I also really confiused the way sqlite works. Because if run the following query i get the week no as 00

SELECT strftime('%W','2012-01-01');

and week no as 01 instead of 00 for the following query

SELECT strftime('%W','2012-01-02');

Could somebody explain why the sqlite behaves like this.

Answer 1

Try this out:

select * from t1;

+------------+
|   ADate    |
+------------+
| 2012-01-04 |
| 2012-01-10 |
| 2012-01-19 |
| 2012-01-22 |
| 2012-01-01 |
| 2012-01-01 |
+------------+

select 
    strftime('%W', aDate) WeekNumber,
    max(date(aDate, 'weekday 0', '-7 day')) WeekStart,
    max(date(aDate, 'weekday 0', '-1 day')) WeekEnd,
    count(*) as GroupedValues
from t1
group by WeekNumber;

+------------+------------+------------+------------+
| WeekNumber | WeekStart  |  WeekEnd   | WeekNumber |
+------------+------------+------------+------------+
|         00 | 2011-12-25 | 2011-12-31 |          2 |
|         01 | 2012-01-01 | 2012-01-07 |          1 |
|         02 | 2012-01-08 | 2012-01-14 |          1 |
|         03 | 2012-01-15 | 2012-01-21 |          2 |
+------------+------------+------------+------------+

To be honest... I don't know why 2012-01-01 is week 0 and 2012-01-02 is week 1. Sounds very weird, particularly if the week starts on sundays! :s