group by different time period than functions allow

group by different time period than functions allow

am 11.06.2009 17:30:16 von Andrey Dmitriev

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=gcdmg-mysql-2@m.gmane.org

RE: group by different time period than functions allow

am 11.06.2009 18:34:19 von Rolando Edwards

SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(D=
ATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FR=
OM (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:

lwdba@localhost (DB information_schema) :: SELECT DT DT1,DATE_ADD(DT,INTERV=
AL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYO=
FWEEK(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 F=
ROM (SELECT 4 DOW, 9 HR) AA) AAA) B
Where A.mydate >=3D 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
redwards@logicworks.net

-----Original Message-----
From: Andrey Dmitriev [mailto:admitriev@mentora.biz]=20
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

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks=
..net


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: group by different time period than functions allow

am 11.06.2009 18:37:31 von Rolando Edwards

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 F=
ROM (SELECT 4 DOW, 9 HR) AA) AAA) B
Where A.mydate >=3D 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
redwards@logicworks.net


-----Original Message-----
From: Rolando Edwards [mailto:redwards@logicworks.net]=20
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(D=
ATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FR=
OM (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:

lwdba@localhost (DB information_schema) :: SELECT DT DT1,DATE_ADD(DT,INTERV=
AL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYO=
FWEEK(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 F=
ROM (SELECT 4 DOW, 9 HR) AA) AAA) B
Where A.mydate >=3D 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
redwards@logicworks.net

-----Original Message-----
From: Andrey Dmitriev [mailto:admitriev@mentora.biz]=20
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

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks=
..net


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks=
..net


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg