View Full Version : count inserted records for a specific day of the week

02-10-2013, 05:05 PM
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


02-11-2013, 01:31 AM
what have you tried? you can specify DAYOFWEEK(yourdatefield) to get the day of week from the date or datetime entered.

02-11-2013, 03:20 AM
Thanks for the reply. I have tried the following:

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!

Old Pedant
02-11-2013, 08:20 PM
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
AND WEEKDAY(timeentry) = 3 ) AS X

And so on.

Learn to use the builtin functions the MySQL gives you. Read the manual!!!

02-11-2013, 10:57 PM
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!