Correction !!! select week(A.mydate),count(A.mystuff) from table A, (SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW, 9 HR) AA) AAA) B Where A.mydate >= B.DT1 And A.mydate < B.DT2 group by week(A.mydate);
Give this one a Try !!! Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx redwa...@logicworks.net -----Original Message----- From: Rolando Edwards [mailto:redwa...@logicworks.net] Sent: Thursday, June 11, 2009 12:34 PM To: Andrey Dmitriev; mysql@lists.mysql.com Subject: RE: group by different time period than functions allow SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW,9 HR) AA) A; This query will produce the previous Wed at 9AM to the next Wed 9AM. Run it in the MySQL Client and note the output: lw...@localhost (DB information_schema) :: SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW,9 HR) AA) A; +---------------------+---------------------+ | DT1 | DT2 | +---------------------+---------------------+ | 2009-06-10 09:00:00 | 2009-06-17 09:00:00 | +---------------------+---------------------+ 1 row in set (0.00 sec) Note the subquery SELECT 4 DOW,9 HR The DAYOFWEEK function returns 1 for Sun, 2 for Mon, 3 for Tue, 4 for Wed, ..., 7 for Sat If you need Fri to Fri change the DOW to 5 If you need Mon to Mon change the DOW to 2 If you need 3AM to 3AM change the HR to 3 Applying it to your query, it should look something like this select week(A.mydate),count(A.mystuff) from table A, (SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW, 9 HR) AA) AAA) B Where A.mydate >= B. DT1_9AM And A.mydate < B. DT2_9AM group by week(A.mydate); Give it a Try !!! Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM & Skype : RolandoLogicWorx redwa...@logicworks.net -----Original Message----- From: Andrey Dmitriev [mailto:admitr...@mentora.biz] Sent: Thursday, June 11, 2009 11:30 AM To: mysql@lists.mysql.com Subject: group by different time period than functions allow Can someone point a link, or show an example. basically, i have something like select week(mydate), count(mystuff) from table group by week(mydate); however, I need week to start on Wed 9am and end next Wed. What's the easiest way to accomplish that? thanks, andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
