Summing inside Redshift UDF

Question

I have a dates tables, columns:

1. date: the date e.g. '2018-06-29'
2. business_day: 1 or 0 to indicate if date is business day or not

I want to create a User-Defined function in Redshift that outputs between two dates how many business days. In a select statement is will look like :

select business_days(start_date timestamp, end_date timestamp) as integer

for example:

2018-06-29 is a friday business day
2018-06-30 saturday non business day
2018-07-01 sunday non business day
2018-07-02 business day

select business_days( '2018-06-29', '2018-07-02') should output 2

Redshift does not allow aggregates inside a function and I am looking for a workaround.

Answer 1

Amazon Redshift User-Defined Functions cannot access the network, nor can they access data stored in tables.

As an alternative approach, you could create a Python function that can calculate the difference between two dates.

If you merely wish to skip weekends, you could Count number of days between dates, ignoring weekends.

If you have more complex logic, such as skipping holidays that vary by year, you will need to supply that information to the Python function, such as including the special dates within the function itself (eg in a list of holiday days).