Something (hopefully useful) for the all the Tableau repository warriors:
Table schedules holds, as the name implies, the definition of schedules in Tableau Server.
Unfortunately for the SQL user, the information about which day of the week and which day of a month a schedule should trigger an action are encoded in two number columns via binary encoding.
So, e.g. if the right-most bit of the value in day_of_week_mask is set, then this schedule triggers stuff on a Sunday.
While this is very efficient for the Tableau programmer, in SQL we just see a number, say 124 and need to somehow figure out, which days are covered by that.
Same story for day_of_month_mask just with more encoded values.
There are plenty of ways to approach this.
Here’s my SQL snippet for this, using binary AND operations ( & operator) and binary literals B’00000001' ) as well as type casting ( ::bit(7), ::char(31)) .
select
s.id
, s."name"
, s.day_of_week_mask
, case when (s.day_of_week_mask::bit(7) & B'0000001' = B'0000001' )
then 'X' else ' ' end SUN
, case when (s.day_of_week_mask::bit(7) & B'0000010' = B'0000010' )
then 'X' else ' ' end MON
, case when (s.day_of_week_mask::bit(7) & B'0000100' = B'0000100' )
then 'X' else ' ' end TUE
, case when (s.day_of_week_mask::bit(7) & B'0001000' = B'0001000' )
then 'X' else ' ' end WED
, case when (s.day_of_week_mask::bit(7) & B'0010000' = B'0010000' )
then 'X' else ' ' end THU
, case when (s.day_of_week_mask::bit(7) & B'0100000' = B'0100000' )
then 'X' else ' ' end FRI
, case when (s.day_of_week_mask::bit(7) & B'1000000' = B'1000000' )
then 'X' else ' ' end SAT
, s.day_of_month_mask
, reverse(replace (replace(s.day_of_month_mask::bit(31)::char(31), '0', '_') , '1', 'X')) days_of_month
from public.schedules s
which leads to an output like this:
id |name |day_of_ |sun|mon|tue|wed|thu|fri|sat|day_of_ | days_of_month |
| |week_mask| | | | | | | |month_mask|
---+----------------------------------+---------+---+---+---+---+---+---+---+---------+-------------------------------+
33|Tuesday - Saturday (2:00 AM) | 124| | |X |X |X |X |X | 0|_______________________________|
353|Monday - 7:45am | 2| |X | | | | | | | |
323|MigrateSystemUserIdentities | 0| | | | | | | | 0|_______________________________|
342|Testing | 1|X | | | | | | | 0|_______________________________|
349|Daily - 23:55pm Subscription | 127|X |X |X |X |X |X |X | 0|_______________________________|
354|Weekly - Monday 7AM | 2| |X | | | | | | 0|_______________________________|
352|1st day of every month at 11:30 AM| 0| | | | | | | | 1|X______________________________|
348|Monthly - First 7 Days | 0| | | | | | | | 127|XXXXXXX________________________|
The reverse function makes sure that days_of_month can be read from left to right.
What I found remarkable is how similar this is to how SAP encodes calendar information in their TFACS table as discussed in SAP Factory Calendar in Snowflake and Finding answers on workdays.
There you go, now you know!