SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist comfixit's Avatar
    Join Date
    Dec 2004
    Location
    Pasadena
    Posts
    537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How do I group date results by week starting on a specific day?

    I have the following query that produces the desired result, except I would like to be able to control which 7 days constitute a grouping. Specifically I would like to run it Friday - Thursday or Monday - Sunday depending on the situation.

    Here is the query I have so far:

    Code:
    SELECT COUNT( * ) AS reports_in_week, DATE( DATE_ADD( created, INTERVAL( 1 - DAYOFWEEK( created ) )
    DAY ) ) AS Start_Date, DATE( DATE_ADD( created, INTERVAL( 7 - DAYOFWEEK( created ) )
    DAY ) ) AS End_Date
    FROM posts
    GROUP BY WEEK( created )
    LIMIT 0 , 30
    and here is what some of the results look like:

    Code:
    # 	Start_Date 	   End_Date
    3 	2008-12-28 	2009-01-03
    22 	2009-01-04 	2009-01-10
    13 	2009-01-11 	2009-01-17
    10 	2009-01-18 	2009-01-24
    11 	2009-01-25 	2009-01-31
    3 	2009-02-01 	2009-02-07
    1 	2009-02-08 	2009-02-14
    2 	2009-02-15 	2009-02-21
    12 	2009-02-22 	2009-02-28
    So what I need to know is how to manipulate the query so I can control the start date to be Monday or Friday (or whatever depending on what is needed)

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you could group by this
    Code:
    DATE_FORMAT(CASE
                WHEN DAYOFWEEK(created) < 4 
                THEN created - INTERVAL 1 WEEK
                ELSE created
                END), '&#37;Y%U')


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •