CODEWITHSUNDEEP
sqldatecalendarsnowflake-cloud-data-platformdatediff
Jay Tilala
Jay Tilala

Reputation: 181

Working days between two dates in Snowflake

Is there any ways to calculate working days between two dates in snowflake without creating calendar table, only using "datediff" function

Upvotes: 8

Views: 18768

Answers (4)

Nico
Nico

Reputation: 66

I have a way to calculate the number of business hours that elapse between a start time and end time but it only works if you make the following assumptions.

  1. Asssume only 1 time zone for all timestamps
  2. Any start or end times that occur outside of business hours should be rounded to nearest business hour time. (I.e. Assuming a schedule of 10:00am - 6:00 pm, timestamps occurring from midnight to 9:59am should be rounded to 10am, times after 6:00pm should be set to the next day at 10:00am)
  3. Timestamps that occur on the weekends should be set to the opening time of the next business day. (In this case Monday at 10:00am)
  4. My model does not account for any holidays.

If these 4 conditions are met then the following code should be enough for a rough estimate of business hours elapsed.

(DATEDIFF(seconds, start_time, end_time) --accounts for the pure number of seconds in between the two dates
- (DATEDIFF(DAY, start_time,end_time) * 16 * 60*60) --For every day between the two dates, we need to subtract out X number of hours. Where X is the number of hours not worked in a day. (i.e. for a standard 8 hour work day, set X =16. For a 10 hour day, set X = 14, etc.) We multiple by (60*60*16) to convert days into seconds.   
 - (DATEDIFF(WEEK, start_time, end_time)*(8*2*60*60)) --This accounts for the fact that weekends are not work days. Which is why we need to subtract an additional 8 hours for Saturday and Sunday.
)/(60*60*8) --We then divide by 60*60*8 to convert the business seconds into business days. We use 8 hours here instead of 24 hours since our "business day" is only 8 hours long.

Upvotes: 0

Paul Vernon
Paul Vernon

Reputation: 3901

The best way to count the number of Sundays between two dates is possibly as follows:

CREATE OR REPLACE FUNCTION SUNDAYS_BETWEEN(a DATE,b DATE)
RETURNS INTEGER
AS $$
FLOOR( (DAYOFWEEKISO(a) + DATEDIFF('days',a,b)) / 7 ,0)  
$$

The above is better than using DATEDIFF(WEEK because the output of that function changes if the WEEK_START session parameter is altered away from the legacy default of 0

Upvotes: 0

Henry Meng
Henry Meng

Reputation: 161

Here's an article with a calendar table solution that also includes a UDF to solve this in Snowflake (the business days are hard-coded, so that does require some maintenance, but you don't have to maintain a calendar table at least):

https://medium.com/dandy-engineering-blog/how-to-calculate-the-number-of-working-hours-between-two-timestamps-in-sql-b5696de66e51

Upvotes: 3

Jay Tilala
Jay Tilala

Reputation: 181

After doing research work on snowflake datediff function, I have found the following conclusions.

  1. DATEDIFF(DAY/WEEK, START_DATE, END_DATE) will calculate difference, but the last date will be considered as END_DATE -1.
  2. DATEDIFF(WEEK, START_DATE, END_DATE) will count number of Sundays between two dates.

By summarizing these two points, I have implemented the logic below.

SELECT 
    ( DATEDIFF(DAY, START_DATE, DATEADD(DAY, 1, END_DATE))
    - DATEDIFF(WEEK, START_DATE, DATEADD(DAY, 1, END_DATE))*2 
    - (CASE WHEN DAYNAME(START_DATE) != 'Sun' THEN 1 ELSE 0 END)
    + (CASE WHEN DAYNAME(END_DATE) != 'Sat' THEN 1 ELSE 0 END)
    ) AS WORKING_DAYS

Upvotes: 9

Related Questions