count inserted records for a specific day of the week

[mbarandao]

Hello all:

I would like to query my mysql table for a total number of records inserted during a specified day of the week within a specified time period.

More clearly: count number records inserted on Tuesdays during the time period of 2013-01-31 - 2013-02-03. The first day of the week being Monday.

My date field in mysql is formated as 0000-00-00 00:00:00

Any thoughts on this is appreciated

Best,
Mossa

[guelphdad]

what have you tried? you can specify DAYOFWEEK(yourdatefield) to get the day of week from the date or datetime entered.

[mbarandao]

Thanks for the reply. I have tried the following:

PHP Code:

select count(*) from history
where timeentry between "2012-02-03" and "2012-02-09" 
and dayofweek(timeentry) = 3 


However, I would like to specify that my beginning day of the week is Monday so therefore, if I'm looking for all of the visits that occurred on "Tuesdays" for the "past month", Tuesday would be represented by 2. Is this thinking correct?

Additionally, I really need the above statement to get the Average visits for Tuesdays for the past specified time period. My statement above does not really give me the average. So essentially, I need some guidance in that area as well.

Any further thoughts on this is appreciated!
Mossa

[Old Pedant]

MySQL doesn't support different days for the beginning of the week (some other databases and languages do, but not MySQL). So you would have to use the MySQL standard, no choice: 0 is Monday, 1 is Tuesday, etc., 6 is Sunday.

Average visits for Tuesday in (say) 2013:

SELECT AVG(X.visits)
FROM ( SELECT DATE(timeentry), COUNT(*) AS visits
       FROM history
       WHERE YEAR(timeentry) = 2013
       AND WEEKDAY(timeentry) = 1 ) AS X

Average visits for Thursday for last 3 months:

SELECT AVG(X.visits)
FROM ( SELECT DATE(timeentry), COUNT(*) AS visits
       FROM history
       WHERE timeentry >= DATE_SUB( CURDATE(), INTERVAL 3 MONTH) 
       AND WEEKDAY(timeentry) = 3 ) AS X

And so on.

Learn to use the builtin functions the MySQL gives you. Read the manual!!!
http://dev.mysql.com/doc/refman/5.5/...functions.html

[mbarandao]

Old Pedant, I appreciate your thoughts on this. Once again, you have come to my aide. I'm much appreciative. I will read more on the link you provided. Thanks again!

Best,
Mossa