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

[adam_k]

select count(*) from table_name
where date between "2013-01-31" and "2013-02-04" 
and dayofweek(date) = 3 

[mbarandao]

Thanks for the reply.

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 statement to get the Average visits for Tuesdays (or any specified day of the week) for the past specified time period. The statement above does not really give me the average. So essentially, I need so guidance in that area as well.

Any further thoughts on this is appreciated!
Mossa

[adam_k]

You mentioned nothing about average.
I guess that would make it:

SELECT avg(counter) FROM
(SELECT weekofyear(timeentry) AS 'week', count(*) AS 'counter' FROM history
    WHERE timeentry between "2012-02-03" and "2012-02-09"  
    and dayofweek(timeentry) = 3
GROUP BY weekofyear(timeentry)) a;

dayofweek will return values based on ODBC standard, as described here: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek
so Monday being 1st day of the week doesn't make a difference.

[mbarandao]

Thanks adam K. Sorry, I realized afterward that I needed an average. I appreciate your help.

Best,
Mossa

[adam_k]

If this has answered your question, please mark this thread as solved.
Thanks.