WEEKDAY_CALENDAR
Description
WEEKDAY_CALENDAR produces a new weekday calendar using the given configuration.
A weekday calendar specifies on which weekdays (e.g., 'Monday') work is done. Additionally, the weekday calendar can be used to specify shifts (e.g., '06:30-17:00') for each weekday.
The weekday calendar is used as input for functions like REMAP_TIMESTAMPS. In these contexts the calendar allows to restrict the accounted DATEs which are used for the date based calculations. By this, only valid weekdays (and their respective shifts) as configured in the weekday calendar will be considered.
Syntax
We can create a new weekday calendar by using either of the two syntaxes shown below. The first syntax creates a single weekday calendar from different day and day time range, whereas the second syntax creates multiple weekday calendars by making use of a table that has at least 4 columns named IDENT, WEEKDAY, SHIFT_BEGIN, and SHIFT_END. More details about the table and the required columns can be found below.
WEEKDAY_CALENDAR ( day [ day_time ] ... )
day: Restricts the weekday. One of
MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAYorSUNDAYday_time: Restricts the time of the day in the format HH:MM-HH:MM. If no time is specified, a full day (00:00-24:00) is assumed.
WEEKDAY_CALENDAR ( calendar_table )
calendar_table: Table with four columns.
IDENT: STRING column specifying the calendar specification ID.
WEEKDAY: Restricts the weekday. One of
MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY,SATURDAYorSUNDAYSHIFT_BEGIN: Column specifying begin of shift in milliseconds after midnight as INTEGER or in "HH:MM" format as STRING.
SHIFT_END: Column specifying end of shift in milliseconds after midnight as INTEGER * or in "HH:MM" format as STRING.
If the begin and end of the shift are equal, an empty shift is specified and will thus be handled as if the given weekday was not set at all.
The shift 00:00-24:00 specifies a shift lasting the entire day (i.e., 24 hours).
Multiple shifts during one weekday are currently not supported.
If a weekday is specified more than once, only the last specification is respected (i.e., previous specifications are overwritten).
Tips
Please note, before simply writing 'MONDAY TUESDAY 08:00-16:30' (i.e., without the 'WEEKDAY_CALENDAR' keyword) produced a weekday calendar as well (for valid weekdays Monday and Tuesday from 8:00 to 16:30). When combining this calendar specification with the time unit 'DAYS', the specified shifts are ignored for all computations. This behavior is inconsistent with regards to the other time units. Nevertheless, if this behavior is desired (e.g., to compute the number of valid weekdays on which work was done) you can use the weekday calendar specification without the 'WEEKDAY_CALENDAR' keyword and the time unit 'DAYS'.
We recommend (if applicable) to use the WORKDAY_CALENDAR for such computations. In the future, the weekday calendar specification without the 'WEEKDAY_CALENDAR' keyword will not be supported in new calendar related features such as calendar intersection (see INTERSECT) for example. [ DEPRECATED | Use the weekday calendar specification with the 'WEEKDAY_CALENDAR' keyword].
Examples
[1] For a constant timestamp, calculate the number of days since 1970-01-01 by using the REMAP_TIMESTAMPS function in conjunction with a weekday calendar. | ||||||
| ||||||
|
[2] Calculate the number of working days between two dates using the REMAP_TIMESTAMPS function. It is assumed that working days are from Monday to Friday (with 24h a day). | |||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||
|
[3] Example for using a weekday calendar with shifts as input to the REMAP_TIMESTAMPS function. The 1970-01-01 is a Thursday. | ||||||
| ||||||
|
[4] For a constant timestamp, calculate the number of hours since 1970-01-01 by using a weekday calendar with specified shifts (again, using the REMAP_TIMESTAMPS function). | ||||||
| ||||||
|
[5] If a weekday is specified more than once, only the last specification is respected (i.e., previous specifications are overwritten). | ||||||
| ||||||
|
[6] The following example shows the behavior of the weekday calendar without the ' | ||||||||||
| ||||||||||
|
[7] WEEKDAY_CALENDAR(calendar_table) syntax. This is used in REMAP_TIMESTAMPS along with a Calendar IDs column. Weekday Calendar is created for different identifiers present in the Calendar IDs column. | |||||||||||||||||||||||||
| |||||||||||||||||||||||||
|
[8] WEEKDAY_CALENDAR(calendar_table) syntax. This is used in REMAP_TIMESTAMPS along with a Calendar IDs column. The shifts in the weekday calendar table are in "HH:MM" format. | |||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||
|