SQLite: Get average weekdays between two dates

Question

I have a table called Trucks with two date columns: Arrival and Released. I can calculate the average number of days between those dates like so:

SELECT avg(julianday(released) - julianday(arrival)) 
FROM Trucks

However, I only want to count weekdays--that is, I want to ignore Saturdays and Sundays. Is there any way to do this in SQLite? I have seen solutions for more robust RDBMSs like Oracle and MSSQL, but none that work for SQLite.

Answer 1

I found this post while searching to see if SQLite had a weekday function. If you use the top answer you're not likely to get a very solid value for days of the week. Here is the code I used for my project, which calculates workdays between ticket creation and closure in SpiceWorks:

SELECT t.id
,t.summary
,t.category
,u.email
,(CAST(strftime('%j', t.closed_at) as INTEGER) - CAST(strftime('%j', t.created_at) as INTEGER) - /*I can't figure it out, but julianday() wasn't giving me the correct distances for some numbers. I used the day of the year, instead, which resolved things as expected*/
    CASE                                                    
    WHEN CAST(strftime('%W', t.closed_at) as INTEGER) - CAST(strftime('%W', t.created_at) as INTEGER) = 0 THEN 0 /*If they are in the same week then there is no weekend to count. If you closed a ticket, that was opened on a Monday, on Saturday, then you must of worked on Saturday and therefore counts as work day the same as if you finished it on Monday*/
    WHEN CAST(strftime('%w', t.created_at) as INTEGER) = 0 THEN (CAST(strftime('%W', t.closed_at) as INTEGER) - CAST(strftime('%W', t.created_at) as INTEGER)) * 2 - 1 /*If they made their ticket on Sunday, don't count that Sunday*/
    WHEN CAST(strftime('%w', t.created_at) as INTEGER) = 6 THEN (CAST(strftime('%W', t.closed_at) as INTEGER) - CAST(strftime('%W', t.created_at) as INTEGER)) * 2 - 2 /*If they made their ticket on Saturday, don't count that Saturday*/
    ELSE (CAST(strftime('%W', t.closed_at) as INTEGER) - CAST(strftime('%W', t.created_at) as INTEGER)) * 2 /*Ignoring the possibility for closed dates that happen before open dates, take the number of weeks between each date and assume each had a Saturday and Sunday within them*/
    END) as 'Week Days Elapsed' /*This equation in full represents: Full_Distance_In_Days_Between - Number of weekend days calculated*/
    FROM Tickets as t
    LEFT JOIN Users as u
    on t.created_by = u.id
    WHERE  strftime('%Y-%m', t.created_at) = strftime('%Y-%m', 'now') 

Answer 2

The raw difference in days must be adjusted depending (only) on the week days of the arrival and released days:

rel arr|0 1 2 3 4 5 6
-------+-+-+-+-+-+-+-
  0    |2 0 0 0 0 0 1
  1    |2 2 0 0 0 0 1
  2    |2 2 2 0 0 0 1
  3    |2 2 2 2 0 0 1
  4    |2 2 2 2 2 0 1
  5    |2 2 2 2 2 2 1
  6    |2 2 2 2 2 2 2

This number can be computed with a simple expression (here: the inner CASE expression):

SELECT
AVG(julianday(released) - julianday(arrival) -
    CASE WHEN julianday(released) = julianday(arrival) THEN 0
    ELSE (CAST((julianday(released) - julianday(arrival)) / 7 AS INTEGER) * 2
         ) +
      CASE WHEN strftime('%w', arrival) <= strftime('%w', released) THEN 2
           ELSE strftime('%w', arrival) = '6'
      END
    END)
FROM trucks

(A boolean expression like x='6' returns 0 or 1.)

Answer 3

Okay, I figured out a very messy solution using lots of nested CASE statements. It checks the weekday number of released and then the weekday number of arrival and does a calculation to figure out how many weeks have passed. After that, I add 0, 1, or 2 as a base number of weekend days that must have passed between those two days (ie from Friday to Monday is always +2 weekend days, even if less than a whole week has passed between the dates).

Here it is, in case anyone finds it useful. Quite possibly the ugliest SQL I have ever written. If anyone figures out a better way, please let me know.

(Edited for simplicity based on CL's feedback)

SELECT 
avg(
julianday(released) - julianday(arrival) - 

CASE WHEN julianday(released) = julianday(arrival) THEN 0 ELSE
CASE strftime('%w', released) 
WHEN '0' THEN
 CASE strftime('%w', arrival) 
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)    
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)         
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)           
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)         
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)         
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)          
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 1)
 END
WHEN '1' THEN
 CASE strftime('%w', arrival) 
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)  
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)          
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)           
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)           
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)           
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)           
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 1)
 END
WHEN '2' THEN
 CASE strftime('%w', arrival) 
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)      
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)           
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)            
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)            
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)       
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 1) 
 END
WHEN '3' THEN
 CASE strftime('%w', arrival) 
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)      
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)           
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)            
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)            
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 1) 
 END
WHEN '4' THEN
 CASE strftime('%w', arrival) 
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)      
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)           
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)           
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 0)           
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 1)
 END
WHEN '5' THEN
 CASE strftime('%w', arrival)
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)      
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)           
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 1)
 END
WHEN '6' THEN
 CASE strftime('%w', arrival) 
 WHEN '0' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)      
 WHEN '1' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)           
 WHEN '2' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '3' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '4' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '5' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2)            
 WHEN '6' THEN ((cast((julianday(released)-julianday(arrival)) / 7 as int) * 2) + 2) 
 END
END
END

), avg(julianday(released)-julianday(arrival))
from trucks

(Note: the avg(julianday(released)-julianday(arrival)) at the end is just for testing purposes to show that the new calculated average is in fact less than a straight average of the difference between the two dates).