MySQL Count Weekday occurrences

Each SQL dialect is different in some way, shape, form, or fashion from the next flavor. Some dialects have this function, while others have that function. In this post, I cover porting over Oracle SQL to MySQL in order to count the number of occurrences of a specific weekday found in the current given month (at the time of writing) purely as a learning exercise focused on MySQL DATE functions and the WITH clause…

OS and DB used:

• Linux Mint 20 “Ulyana”
• MySQL 8.0.23

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

I was inspired to write this blog post – learning many things along the way – by this fantastic YouTube video:

Even though the examples in the video use the Oracle Database – which I have not used much – I still learned a lot. And, the challenge of porting Oracle SQL over to MySQL was a perfect opportunity for me to learn even more about both implementations.

MySQL Count Weekday occurrences: Data Needed

The end goal of the exercise is to count the number of occurrences of a specific Weekday found in a certain month. To solve the problem, we need 3 metrics:

• The first day of the given month
• The last day of the given month
• The number of days in the given month.

1. First day of the month

To my knowledge, MySQL does not have a first day of the month type of function. But, we can easily retrieve the first day of the current month by using a combination of many of the built-in DATE functions.

Let’s look at the below query in detail:

3 functions are used in the above query to retrieve the first day of the month for the current month as provided by the NOW() function:

• DATE_ADD() – Performs date arithmetic
• NOW() – Provides the current date and time
• DAY() – The numeric day of the month (1 – 31)

A combination of the DATE_ADD(), NOW(), and DAY() functions produce a TIMESTAMP value. However, I need just the date part from the timestamp. By wrapping the entire expression in the DATE() function call, we are returned the date value:

2. Last day of the month

Fortunately, MySQL does have a LAST_DAY() date function which returns the last day of the month.

Passing the NOW() function as the target date parameter for LAST_DAY() yields the last day of the month. Again, I call the DATE() function on the entire expression, retrieving the date value of the result:

3. Number of days in the month

We now have 2 of the 3 needed metrics:

To calculate the number of days between the first day of the month and the last day of the month – which is the number of days in the month – we can simply subtract the above metrics from each other, then add 1 to that result. The current time of this writing is March 2021, which does have 31 days:

MySQL Count Weekday occurrences: Recursive CTE

One of the next sets of data we need is the first day of the month, repeated a number of times equal to the number of days in the target month. The YouTube video author used the Oracle SQL CONNECT BY clause for this requirement. But, MySQL does not have a CONNECT BY clause.

So what can I use that MySQL does have to reproduce similar behavior of the CONNECT BY clause?

Leveraging a recursive WITH clause (also known as a Common Table Expression or CTE).

Let’s first generate a table of the number of days in the current month (31) using a recursive CTE:

As long as the ‘num_days’ CTE is in scope, it is available for any follow-up queries and can be named as the data source in the FROM clause as shown in this query:

Essentially, if the ‘d_number’ column value is less than the value of the last day of the month (31 in this example) – which is evaluated by the second SELECT statement’s WHERE clause conditional – 1 is added to the recurring value. An important note: the CTE is named ‘num_days’ and so is the target table/result set in the FROM clause of the 2nd SELECT query. This is recursion.

I have provided the exact verbiage from the official documentation (see link in closing section) for a better explanation:

“The first SELECT produces the initial row or rows for the CTE and does not refer to the CTE name. The second SELECT produces additional rows and recurses by referring to the CTE name in its FROM clause. Recursion ends when this part produces no new rows. Thus, a recursive CTE consists of a nonrecursive SELECT part followed by a recursive SELECT part.”

Replicating the first day of the month value with a recursive CTE

Now we must replicate the first day of the month value an equal number of times as there are days in the target month. Since we have a recursive CTE, we can include this value in both SELECTs column lists. In the recursive SELECT, we increment the 1 present in the non-recursive SELECT by 1.

Also, we must filter in the WHERE clause of the recursive SELECT query, monitoring (sort of) the ‘d_number’ value. So long as ‘d_number’ evaluates to a number that is less than the number of the days in the month, the WHERE clause result is TRUE, and the recursive CTE continues to produce rows. However, once ‘d_number’ is equal to the number of days in the month, on the next iteration, the WHERE clause conditional evaluates to FALSE and recursion stops.

I’ll SELECT all available rows from the ‘n_days’ CTE for a better visual understanding:

The purpose of the replicated first day of the month value is that now we can perform date math by adding 1 to each row, representing each day of the month. However, there is a small problem. With the addition values ranging from 1 to 31, when added to the respective first day of the month value, each resulting date will be 1-day value greater than intended.

The fix is to simply subtract 1 from the ‘d_number’ column (I think of it as a counter of sorts), for each of the rows returned by the CTE:

By subtracting 1 from each of the first day of the month values, we now have a number to use in date math. When adding the 2 columns together, we receive a DATE value for each day of the month. Again, the DATE() function is used on the overall expression, returning a DATE value:

MySQL Count Weekday occurrences: Derived Table and more built-in DATE functions

Note: For the next several queries, assume the CTE we have been using so far is available for use as it will not be shown with the following queries in order to provide better on-screen reading and avoid unnecessary repetition.

Creating a DERIVED TABLE from the SELECT query which uses the ‘n_days’ CTE in its FROM clause, along with the DAYNAME() date function, we can retrieve the Weekday name for each of the 31 rows (every day of the month):

Suppose I am interested in knowing how many Tuesdays and Fridays are in March 2021 (the target example for this post)? For starters, I’ll use the IN predicate in the WHERE clause and filter for just those 2 Weekday names:

Then use the COUNT() aggregate function and GROUP BY the DAYNAME() function return value:

Which returns the number of occurrences for the Weekdays ‘Tuesday’ and ‘Friday’.

Informational Resources

• MySQL – Finding First day and Last day of a Month
• MySQL Date and Time Functions
• MySQL WITH (Common Table Expressions)

I thoroughly enjoyed writing this post, learning a great deal about MySQL CTE’s, date functions, and the Oracle Database. Please let me know if you see any mistakes in the code or explanation by commenting below.

Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.