Member Avatar for 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

  • 2 Contributors
  • 5 Replies
  • 272 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by adam_k

Dani AI

Generated

Two slightly different meanings of “average for Tuesdays” are common, so pick the one you want and use the matching query.

WEEKDAY vs DAYOFWEEK

  • DAYOFWEEK(date) returns 1=Sunday .. 7=Saturday (ODBC-style). That’s what referred to.
  • WEEKDAY(date) returns 0=Monday .. 6=Sunday (so Tuesday is 1). If you prefer Monday as first day, use WEEKDAY (or WEEKDAY(...) + 1 for a 1-based value).

Average of weekly Tuesday counts (average of per-week counts)

  • Count Tuesdays per calendar week (with Monday as week start), then take the AVG of those weekly counts. This averages only over weeks that exist in the range:
SELECT AVG(weekly.cnt) AS avg_tuesdays
FROM (
  SELECT COUNT(*) AS cnt
  FROM history
  WHERE timeentry >= '2013-01-31'
    AND timeentry <  DATE_ADD('2013-02-03', INTERVAL 1 DAY)
    AND WEEKDAY(timeentry) = 1   -- Tuesday (0=Mon)
  GROUP BY YEARWEEK(timeentry, 1)  -- mode=1 => weeks start Monday
) AS weekly;

Average per-Tuesday including zeros (divide total visits by number of Tuesdays in the range)

  • This treats every Tuesday in the interval (even with 0 visits) the same. Compute total Tuesday hits, compute how many Tuesdays occur between start and end, then divide. A safe method uses a small variable sequence:
SET @start='2013-01-31'; SET @end='2013-02-03'; SET @wd=1; -- WEEKDAY: 1=Tue
SELECT @total := (SELECT COUNT(*) FROM history
                  WHERE timeentry >= @start AND timeentry < DATE_ADD(@end, INTERVAL 1 DAY)
                    AND WEEKDAY(timeentry)=@wd);
SELECT @first := DATE_ADD(@start, INTERVAL ((@wd - WEEKDAY(@start) + 7) % 7) DAY);
SELECT @occ := IF(@first > @end, 0, FLOOR(DATEDIFF(@end, @first)/7) + 1);
SELECT IF(@occ=0, NULL, @total/@occ) AS avg_per_Tuesday;

Practical notes

  • Use "timeentry >= start AND timeentry < DATE_ADD(end, INTERVAL 1 DAY)" to include full end-day (DATETIME caution).
  • Filtering with WEEKDAY(timeentry) calls a function on the column and may prevent index use. For large tables consider adding a stored/generated column for the weekday and index it (MySQL 5.7+), or precompute a dow column at insert time.
  • Use YEARWEEK(...,1) instead of WEEKOFYEAR when your range crosses years so weeks are grouped with the year.

This covers both interpretations; choose the one that matches how you want zeros treated.

Recommended Answers

All 5 Replies

Member Avatar for adam_k 
select count(*) from table_name
where date between "2013-01-31" and "2013-02-04" 
and dayofweek(date) = 3
Member Avatar for 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

Member Avatar for 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.

Member Avatar for mbarandao

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

Best,
Mossa

Member Avatar for adam_k

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.