Скачать или смотреть Finding the Number of Business Days Between Two Dates in BigQuery

Learn how to calculate the `number of business days` between two date columns in BigQuery using a simple yet effective method.
---
This video is based on the question https://stackoverflow.com/q/64319957/ asked by the user 'Maths12' ( https://stackoverflow.com/u/6714667/ ) and on the answer https://stackoverflow.com/a/64320090/ provided by the user 'GMB' ( https://stackoverflow.com/u/10676716/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: how can i find the number of business days between two of my date columns in bigquery?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Find the Number of Business Days Between Two Dates in BigQuery

Calculating the number of business days between two dates might seem like a straightforward task, but it can quickly become challenging—especially in a SQL environment like BigQuery. If you're working with date columns in a dataset and want to count only the business days (Monday to Friday) between those dates, you’re in the right place! In this blog, we'll walk you through the solution step by step using BigQuery SQL syntax.

The Problem

You want to determine the number of business days between two date columns in your BigQuery dataset. For example, if you have an order_date and a pickup_date, you need to calculate how many weekdays lie between those dates, excluding weekends (Saturday and Sunday).

Many users, including yourself, have encountered a common error when trying to use the DATE_DIFF function in BigQuery, which can lead to issues in identifying and counting those business days correctly.

The Solution

One of the easiest ways to calculate the number of business days between two dates in BigQuery is to create a list of dates within that range and filter out the non-business days. Below, we’ll break down the solution into clear steps.

Step 1: Use generate_date_array()

BigQuery provides a function called generate_date_array(), which allows you to create an array of dates between two specified points. This will serve as the foundation for counting your business days.

Step 2: Filter for Business Days

Once you have the list of dates, the next step is to filter out weekends. You can achieve this by checking the day of the week for each date to ensure it is a weekday.

Implementation Example

Here's how to implement this in your SQL query:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Query

SELECT o.order_date, o.pickup_date: This selects the relevant date columns from your orders table.

GENERATE_DATE_ARRAY(): This function generates an array of dates from order_date to pickup_date.

UNNEST(): This function allows you to flatten the array of dates for further processing in SQL.

EXTRACT(DAYOFWEEK FROM dt): This extracts the day of the week for each date, where 1 corresponds to Sunday and 7 to Saturday. We filter to keep only days 2 to 6 (Monday to Friday).

COUNT(*): Finally, we count the number of remaining rows, which gives us the total number of business days.

Conclusion

By leveraging BigQuery's built-in functions, calculating the number of business days between two dates can be accomplished efficiently without running into the complexities of date arithmetic that can lead to errors and frustration. With this method, you can accurately determine the count of weekdays in your datasets.

Now you can confidently implement this solution in your queries without hassle. Hopefully, this guide gives you a clear understanding and a solid approach to performing this operation in BigQuery. If you have any questions or need further assistance, feel free to leave a comment below!