I was prompted to write this article by similar articles on Habré: a production calendar using PostgreSQL and MS SQL . I decided to use a mixed approach. On the one hand, you can store only exceptions for dates and generate a calendar "on the fly", on the other hand, such a calendar can be saved to a permanent table and quickly searched by date or other attributes.
We will use Firebird 3.0 for development, it has significantly expanded the capabilities of PSQL compared to previous versions. All procedures and functions for working with the calendar will be encapsulated in the DATE_UTILS package.
The first step is to create a table to store the standard holiday dates.
CREATE TABLE HOLIDAYS (
ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
AMONTH SMALLINT NOT NULL,
ADAY SMALLINT NOT NULL,
REMARK VARCHAR(255) NOT NULL,
CONSTRAINT PK_HOLIDAYS PRIMARY KEY (ID),
CONSTRAINT UNQ_HOLIDAYS UNIQUE (AMONTH, ADAY
);
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (1, 1, 1, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (2, 1, 7, '');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (3, 2, 23, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (4, 3, 8, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (5, 5, 1, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (6, 5, 9, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (7, 6, 12, ' ');
INSERT INTO HOLIDAYS (ID, AMONTH, ADAY, REMARK)
VALUES (8, 11, 4, ' ');
COMMIT;Such a table will help us automate the process of filling the calendar so as not to add holidays every time as a weekend.
Now let's create a table to store exceptions. It will store both weekdays, which have become weekends, and vice versa, weekends that are designated as working days.
In addition, you can leave an arbitrary note for any date.
CREATE TABLE CALENDAR_NOTES (
BYDATE DATE NOT NULL,
DAY_TYPE SMALLINT NOT NULL,
REMARK VARCHAR(255),
CONSTRAINT PK_CALENDAR_NOTES PRIMARY KEY (BYDATE)
);The DAY_TYPE field indicates the type of date: 0 - business day. 1 - day off, 2 - holiday.
To work with the exception table, we will create 2 stored procedures and place them inside the DATE_UTILS package.
--
PROCEDURE SET_DATE_NOTE (
ADATE DATE,
ADAY_TYPE SMALLINT,
AREMARK VARCHAR(255))
AS
BEGIN
UPDATE OR INSERT INTO CALENDAR_NOTES (BYDATE, DAY_TYPE, REMARK)
VALUES (:ADATE, :ADAY_TYPE, :AREMARK);
END
--
PROCEDURE UNSET_DATE_NOTE (
ADATE DATE)
AS
BEGIN
DELETE FROM CALENDAR_NOTES
WHERE BYDATE = :ADATE;
END
Firebird, unlike PostgreSQL, lacks a special function for generating series of values. Such generation can be done using a recursive CTE, but in this case, we will be limited by the recursion depth. We’ll do it a bit easier, write a special selective stored procedure for generating a sequence of dates and place it inside the DATE_UTILS package.
--
-- 1
PROCEDURE GENERATE_SERIES (
MIN_DATE DATE,
MAX_DATE DATE)
RETURNS (
BYDATE DATE)
AS
BEGIN
IF (MIN_DATE > MAX_DATE) THEN
EXCEPTION E_MIN_DATE_EXCEEDS;
BYDATE = MIN_DATE;
WHILE (BYDATE <= MAX_DATE) DO
BEGIN
SUSPEND;
BYDATE = BYDATE + 1;
END
END
The procedure provides protection against looping; if the minimum date is greater than the maximum, an exception E_MIN_DATE_EXCEEDS will be thrown, which is defined as follows:
CREATE EXCEPTION E_MIN_DATE_EXCEEDS ' ';Now let's move on to generating the calendar on the fly. If the date is contained in the exception table, then the date type and note from the exception table will be inferred. If the date is not in the table of exceptions, but it is present in the table with holiday dates, then we display a note from the table of holidays. Weekends are determined by the number of the day of the week, the rest of the dates are working days. The described algorithm is implemented by the following query
SELECT
D.BYDATE,
CASE
WHEN NOTES.DAY_TYPE IS NOT NULL THEN NOTES.DAY_TYPE
WHEN HOLIDAYS.ID IS NOT NULL THEN 2
WHEN EXTRACT(WEEKDAY FROM D.BYDATE) IN (0, 6) THEN 1
ELSE 0
END AS DATE_TYPE,
COALESCE(NOTES.REMARK, HOLIDAYS.REMARK) AS REMARK
FROM DATE_UTILS.GENERATE_SERIES(:MIN_DATE, :MAX_DATE) D
LEFT JOIN HOLIDAYS
ON HOLIDAYS.AMONTH = EXTRACT(MONTH FROM D.BYDATE) AND
HOLIDAYS.ADAY = EXTRACT(DAY FROM D.BYDATE)
LEFT JOIN CALENDAR_NOTES NOTES
ON NOTES.BYDATE = D.BYDATELet's save this query to a selective stored procedure and add the output of some additional columns
--
PROCEDURE GET_CALENDAR (
MIN_DATE DATE,
MAX_DATE DATE)
RETURNS (
BYDATE DATE,
YEAR_OF SMALLINT,
MONTH_OF SMALLINT,
DAY_OF SMALLINT,
WEEKDAY_OF SMALLINT,
DATE_TYPE SMALLINT,
REMARK VARCHAR(255))
AS
BEGIN
FOR
SELECT
D.BYDATE,
EXTRACT(YEAR FROM d.BYDATE) AS YEAR_OF,
EXTRACT(MONTH FROM d.BYDATE) AS MONTH_OF,
EXTRACT(DAY FROM d.BYDATE) AS DAY_OF,
EXTRACT(WEEKDAY FROM d.BYDATE) AS WEEKDAY_OF,
CASE
WHEN NOTES.DAY_TYPE IS NOT NULL THEN NOTES.DAY_TYPE
WHEN HOLIDAYS.ID IS NOT NULL THEN 2
WHEN EXTRACT(WEEKDAY FROM D.BYDATE) IN (0, 6) THEN 1
ELSE 0
END AS DATE_TYPE,
COALESCE(NOTES.REMARK, HOLIDAYS.REMARK) AS REMARK
FROM DATE_UTILS.GENERATE_SERIES(:MIN_DATE, :MAX_DATE) D
LEFT JOIN HOLIDAYS
ON HOLIDAYS.AMONTH = EXTRACT(MONTH FROM D.BYDATE) AND
HOLIDAYS.ADAY = EXTRACT(DAY FROM D.BYDATE)
LEFT JOIN CALENDAR_NOTES NOTES
ON NOTES.BYDATE = D.BYDATE
INTO BYDATE,
YEAR_OF,
MONTH_OF,
DAY_OF,
WEEKDAY_OF,
DATE_TYPE,
REMARK
DO
SUSPEND;
ENDLet's add several functions for displaying days of the week, names of months and date type in Russian.
--
FUNCTION GET_WEEKDAY_NAME(AWEEKDAY SMALLINT) RETURNS CHAR(2)
AS
BEGIN
RETURN CASE AWEEKDAY
WHEN 1 THEN ''
WHEN 2 THEN ''
WHEN 3 THEN ''
WHEN 4 THEN ''
WHEN 5 THEN ''
WHEN 6 THEN ''
WHEN 0 THEN ''
END;
END
--
FUNCTION GET_MONTH_NAME(AMONTH SMALLINT) RETURNS VARCHAR(10)
AS
BEGIN
RETURN CASE AMONTH
WHEN 1 THEN ''
WHEN 2 THEN ''
WHEN 3 THEN ''
WHEN 4 THEN ''
WHEN 5 THEN ''
WHEN 6 THEN ''
WHEN 7 THEN ''
WHEN 8 THEN ''
WHEN 9 THEN ''
WHEN 10 THEN ''
WHEN 11 THEN ''
WHEN 12 THEN ''
END;
END
--
FUNCTION GET_DAY_TYPE_NAME(ADAY_TYPE SMALLINT) RETURNS VARCHAR(11)
AS
BEGIN
RETURN CASE ADAY_TYPE
WHEN 0 THEN ''
WHEN 1 THEN ''
WHEN 2 THEN ''
END;
ENDWe can now display the calendar using the following query:
SELECT
D.BYDATE AS BYDATE,
D.YEAR_OF,
DATE_UTILS.GET_MONTH_NAME(D.MONTH_OF) AS MONTH_NAME,
D.DAY_OF,
DATE_UTILS.GET_WEEKDAY_NAME(D.WEEKDAY_OF) AS WEEKDAY_NAME,
DATE_UTILS.GET_DAY_TYPE_NAME(D.DATE_TYPE) AS DATE_TYPE,
D.REMARK AS REMARK
FROM DATE_UTILS.GET_CALENDAR(DATE '01.05.2019', DATE '31.05.2019') D
BYDATE YEAR_OF MONTH_NAME DAY_OF WEEKDAY_NAME DATE_TYPE REMARK
=========== ======= ========== ======= ============ =========== ======================
2019-05-01 2019 1
2019-05-02 2019 2
2019-05-03 2019 3
2019-05-04 2019 4
2019-05-05 2019 5
2019-05-06 2019 6 <null>
2019-05-07 2019 7 <null>
2019-05-08 2019 8 <null>
2019-05-09 2019 9
2019-05-10 2019 10
2019-05-11 2019 11 <null>
2019-05-12 2019 12 <null>
2019-05-13 2019 13 <null>
2019-05-14 2019 14 <null>
2019-05-15 2019 15 <null>
2019-05-16 2019 16 <null>
2019-05-17 2019 17 <null>
2019-05-18 2019 18 <null>
2019-05-19 2019 19 <null>
2019-05-20 2019 20 <null>
BYDATE YEAR_OF MONTH_NAME DAY_OF WEEKDAY_NAME DATE_TYPE REMARK
=========== ======= ========== ======= ============ =========== ==================
2019-05-21 2019 21 <null>
2019-05-22 2019 22 <null>
2019-05-23 2019 23 <null>
2019-05-24 2019 24 <null>
2019-05-25 2019 25 <null>
2019-05-26 2019 26 <null>
2019-05-27 2019 27 <null>
2019-05-28 2019 28 <null>
2019-05-29 2019 29 <null>
2019-05-30 2019 30 <null>
2019-05-31 2019 31 <null>
If you need to mark some date as a day off or weekday, use the following query:
EXECUTE PROCEDURE DATE_UTILS.SET_DATE_NOTE(date '05.05.2019', 1, ' ');
To remove a date from the list of exclusions, you need to run a query
EXECUTE PROCEDURE DATE_UTILS.UNSET_DATE_NOTE(date '05.05.2019');
Now create a table to store the production calendar, and write a procedure to fill it.
CREATE TABLE CALENDAR (
BYDATE DATE NOT NULL,
YEAR_OF SMALLINT NOT NULL,
MONTH_OF SMALLINT NOT NULL,
DAY_OF SMALLINT NOT NULL,
WEEKDAY_OF SMALLINT NOT NULL,
DATE_TYPE SMALLINT NOT NULL,
REMARK VARCHAR(255),
CONSTRAINT PK_CALENDAR PRIMARY KEY (BYDATE)
);
-- /
PROCEDURE FILL_CALENDAR (
MIN_DATE DATE,
MAX_DATE DATE)
AS
BEGIN
MERGE INTO CALENDAR
USING (
SELECT
BYDATE,
YEAR_OF,
MONTH_OF,
DAY_OF,
WEEKDAY_OF,
DATE_TYPE,
REMARK
FROM DATE_UTILS.GET_CALENDAR(:MIN_DATE, :MAX_DATE)
) S
ON CALENDAR.BYDATE = S.BYDATE
WHEN NOT MATCHED THEN
INSERT (
BYDATE,
YEAR_OF,
MONTH_OF,
DAY_OF,
WEEKDAY_OF,
DATE_TYPE,
REMARK
)
VALUES (
S.BYDATE,
S.YEAR_OF,
S.MONTH_OF,
S.DAY_OF,
S.WEEKDAY_OF,
S.DATE_TYPE,
S.REMARK
)
WHEN MATCHED AND
(CALENDAR.DATE_TYPE <> S.DATE_TYPE OR
CALENDAR.REMARK <> S.REMARK) THEN
UPDATE SET
DATE_TYPE = S.DATE_TYPE,
REMARK = S.REMARK;
ENDThe procedure for filling the table for storing the calendar is designed in such a way that if a date already exists in it, then the date and note type will be updated only if changes have occurred in the exclusion table or the date has been removed from the exclusion list.
In order for the changes in the exception table to be immediately reflected in the calendar table, we will slightly modify the SET_DATE_NOTE and UNSET_DATE_NOTE procedures. The first change is pretty trivial, we just add another request to the procedure to update the note and date type in the CALENDAR table.
--
PROCEDURE SET_DATE_NOTE (
ADATE DATE,
ADAY_TYPE SMALLINT,
AREMARK VARCHAR(255))
AS
BEGIN
UPDATE OR INSERT INTO CALENDAR_NOTES (BYDATE, DAY_TYPE, REMARK)
VALUES (:ADATE, :ADAY_TYPE, :AREMARK);
--
UPDATE CALENDAR
SET DATE_TYPE = :ADAY_TYPE,
REMARK = :AREMARK
WHERE BYDATE = :ADATE
AND (DATE_TYPE <> :ADAY_TYPE OR REMARK <> :AREMARK);
ENDRemoving the annotation for a date is a little trickier because we have to return the annotation that the date had before it was excluded. To do this, we use the same logic for determining the type of date and comments that were already used in the GET_CALENDAR procedure.
--
PROCEDURE UNSET_DATE_NOTE (
ADATE DATE)
AS
BEGIN
DELETE FROM CALENDAR_NOTES
WHERE BYDATE = :ADATE;
--
MERGE INTO CALENDAR
USING (
SELECT
:ADATE AS BYDATE,
CASE
WHEN HOLIDAYS.ID IS NOT NULL THEN 2
WHEN EXTRACT(WEEKDAY FROM :ADATE) IN (0, 6) THEN 1
ELSE 0
END AS DATE_TYPE,
HOLIDAYS.REMARK AS REMARK
FROM RDB$DATABASE
LEFT JOIN HOLIDAYS ON
HOLIDAYS.AMONTH = EXTRACT(MONTH FROM :ADATE) AND
HOLIDAYS.ADAY = EXTRACT(DAY FROM :ADATE)
) S
ON CALENDAR.BYDATE = S.BYDATE
WHEN MATCHED THEN
UPDATE SET
DATE_TYPE = S.DATE_TYPE,
REMARK = S.REMARK;
END
You can display a calendar from a table using the following query:
SELECT
D.BYDATE AS BYDATE,
D.YEAR_OF,
DATE_UTILS.GET_MONTH_NAME(D.MONTH_OF) AS MONTH_NAME,
D.DAY_OF,
DATE_UTILS.GET_WEEKDAY_NAME(D.WEEKDAY_OF) AS WEEKDAY_NAME,
DATE_UTILS.GET_DAY_TYPE_NAME(D.DATE_TYPE) AS DATE_TYPE,
D.REMARK AS REMARK
FROM CALENDAR D
WHERE D.BYDATE BETWEEN DATE '01.05.2019' AND DATE '31.05.2019'BYDATE YEAR_OF MONTH_NAME DAY_OF WEEKDAY_NAME DATE_TYPE REMARK
=========== ======= ========== ======= ============ =========== ======================
2019-05-01 2019 1
2019-05-02 2019 2
2019-05-03 2019 3
2019-05-04 2019 4
2019-05-05 2019 5
2019-05-06 2019 6 <null>
2019-05-07 2019 7 <null>
2019-05-08 2019 8 <null>
2019-05-09 2019 9
2019-05-10 2019 10
2019-05-11 2019 11 <null>
2019-05-12 2019 12 <null>
2019-05-13 2019 13 <null>
2019-05-14 2019 14 <null>
2019-05-15 2019 15 <null>
2019-05-16 2019 16 <null>
2019-05-17 2019 17 <null>
2019-05-18 2019 18 <null>
2019-05-19 2019 19 <null>
2019-05-20 2019 20 <null>
BYDATE YEAR_OF MONTH_NAME DAY_OF WEEKDAY_NAME DATE_TYPE REMARK
=========== ======= ========== ======= ============ =========== ==================
2019-05-21 2019 21 <null>
2019-05-22 2019 22 <null>
2019-05-23 2019 23 <null>
2019-05-24 2019 24 <null>
2019-05-25 2019 25 <null>
2019-05-26 2019 26 <null>
2019-05-27 2019 27 <null>
2019-05-28 2019 28 <null>
2019-05-29 2019 29 <null>
2019-05-30 2019 30 <null>
2019-05-31 2019 31 <null>That's all. We were able to generate a production calendar on the fly, manage date exceptions, and save the calendar in a table for quick date lookups. The script for creating tables and calendar packages can be found here .