Get previous and next business day practice

preface

In fact, this article has been published and discussed before. In the previous article, I introduced how to build a working day table through the sql statement of postgresql database, and how to use sql syntax to obtain the working day or natural day of a certain day, but I found that many details were missing after reading, Therefore, here we comb the whole process again, hoping to give readers a reference.

This practice is only a solution obtained on a working day provided by individuals. If there is a better solution, you are welcome to discuss and share.

Link to previous article: https://juejin.cn/post/7023008573827481637

Note that the database used is PostgreSql

Pre preparation

Before introducing the specific coding and processing logic, we need to prepare the table structure and related data.

Table design

First, let's review the table structure obtained by this workday table:

-- ----------------------------
-- Table structure for sa_calendar_table
-- ----------------------------
DROP TABLE IF EXISTS "public"."sa_calendar_table";
CREATE TABLE "public"."sa_calendar_table" (
  "calendar_id" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "calendar_year" varchar(10) COLLATE "pg_catalog"."default",
  "calendar_month" varchar(10) COLLATE "pg_catalog"."default",
  "calendar_date" varchar(10) COLLATE "pg_catalog"."default",
  "day_of_week" varchar(10) COLLATE "pg_catalog"."default",
  "day_of_month" varchar(10) COLLATE "pg_catalog"."default",
  "week_of_year" varchar(10) COLLATE "pg_catalog"."default",
  "month_of_year" varchar(10) COLLATE "pg_catalog"."default",
  "quarter_of_year" varchar(10) COLLATE "pg_catalog"."default",
  "is_end_month" varchar(10) COLLATE "pg_catalog"."default",
  "is_end_quarter" varchar(10) COLLATE "pg_catalog"."default",
  "is_end_halfayear" varchar(10) COLLATE "pg_catalog"."default",
  "is_end_year" varchar(10) COLLATE "pg_catalog"."default",
  "operator_id" varchar(50) COLLATE "pg_catalog"."default",
  "operator_name" varchar(50) COLLATE "pg_catalog"."default",
  "operate_date" timestamp(6),
  "res_attr1" varchar(40) COLLATE "pg_catalog"."default",
  "res_attr2" varchar(40) COLLATE "pg_catalog"."default",
  "res_attr3" varchar(40) COLLATE "pg_catalog"."default",
  "res_attr4" varchar(40) COLLATE "pg_catalog"."default",
  "is_work_day" varchar(1) COLLATE "pg_catalog"."default"
)
WITH (fillfactor=100)
;
ALTER TABLE "public"."sa_calendar_table" OWNER TO "postgres";
COMMENT ON COLUMN "public"."sa_calendar_table"."calendar_id" IS 'Primary key';
COMMENT ON COLUMN "public"."sa_calendar_table"."calendar_year" IS 'year';
COMMENT ON COLUMN "public"."sa_calendar_table"."calendar_month" IS 'month';
COMMENT ON COLUMN "public"."sa_calendar_table"."calendar_date" IS 'day';
COMMENT ON COLUMN "public"."sa_calendar_table"."day_of_week" IS 'Day of the week';
COMMENT ON COLUMN "public"."sa_calendar_table"."day_of_month" IS 'What day of the month';
COMMENT ON COLUMN "public"."sa_calendar_table"."week_of_year" IS 'Natural week of the year';
COMMENT ON COLUMN "public"."sa_calendar_table"."month_of_year" IS 'What month of the year';
COMMENT ON COLUMN "public"."sa_calendar_table"."quarter_of_year" IS 'What season of the year';
COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_month" IS 'Month end';
COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_quarter" IS 'End of quarter';
COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_halfayear" IS 'Is it at the end of half a year';
COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_year" IS 'Year end';
COMMENT ON COLUMN "public"."sa_calendar_table"."operator_id" IS 'Operator ID';
COMMENT ON COLUMN "public"."sa_calendar_table"."operator_name" IS 'Operator name';
COMMENT ON COLUMN "public"."sa_calendar_table"."operate_date" IS 'Operation time';
COMMENT ON COLUMN "public"."sa_calendar_table"."res_attr1" IS 'Reserved field 1';
COMMENT ON COLUMN "public"."sa_calendar_table"."res_attr2" IS 'Reserved field 2';
COMMENT ON COLUMN "public"."sa_calendar_table"."res_attr3" IS 'Reserved field 3';
COMMENT ON COLUMN "public"."sa_calendar_table"."res_attr4" IS 'Reserved field 4';
COMMENT ON COLUMN "public"."sa_calendar_table"."is_work_day" IS 'Whether it is a working day, Y Yes, N no(Holidays)';

In addition, here is another skill to teach you how to use postgresql to obtain the table structure of a table:

Postgresql obtains the table structure of a table:

SELECT A
  .attname AS COLUMN_NAME,
  T.typname AS data_type,
  d.description AS column_comment,
  btrim( SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ), '()' ) AS character_maximum_length,
CASE
    
    WHEN A.attnotnull = 'f' THEN
    'NO' 
    WHEN A.attnotnull = 't' THEN
    'YES' ELSE'NO' 
END AS NULLABLE 
FROM
  pg_class C,
  pg_attribute A,
  pg_type T,
  pg_description d 
WHERE
  C.relname = 'Fill in the form name here' 
  AND A.attnum > 0 
  AND A.attrelid = C.oid 
  AND A.atttypid = T.oid 
  AND d.objoid = A.attrelid 
  AND d.objsubid = A.attnum

The following is the calling effect of the statement. Note that the above statement is recommended to annotate all fields before execution.

Fill data

The table structure is not enough. Here we also need to fill in the data. We use the following sql to fill in the data content. The sql statement may be slightly more complex. In addition, there may be missing functions during execution. Since this problem was not encountered during personal use, we skipped:

INSERT INTO sa_calendar_table (
  calendar_id,
  calendar_year,
  calendar_month,
  calendar_date,
  day_of_week,
  day_of_month,
  week_of_year,
  month_of_year,
  quarter_of_year,
  is_end_month,
  is_end_quarter,
  is_end_halfayear,
  is_end_year,
  operator_id,
  operator_name,
  operate_date,
  res_attr1,
  res_attr2,
  res_attr3,
  res_attr4,
  is_work_day 
) SELECT A
.calendar_id,
A.calender_year,
A.calender_month,
A.calendar_date,
A.day_of_week,
A.day_of_month,
A.week_of_year,
A.month_of_year,
A.quarter_of_year,
A.is_end_month,
A.is_end_quarter,
A.is_end_halfayear,
A.is_end_year,
A.operator_id,
A.operator_name,
A.operator_date,
A.res_attr1,
A.res_attr2,
A.res_attr3,
A.res_attr4,
A.is_work_day 
FROM
  (
  SELECT
    gen_random_uuid ( ) AS calendar_id,
    to_char( tt.DAY, 'yyyy' ) AS calender_year,
    to_char( tt.DAY, 'yyyy-mm' ) AS calender_month,
    to_char( tt.DAY, 'yyyy-mm-dd' ) AS calendar_date,
    EXTRACT ( DOW FROM tt.DAY ) AS day_of_week,
    to_char( tt.DAY, 'dd' ) AS day_of_month,
    EXTRACT ( MONTH FROM tt.DAY ) AS month_of_year,
    EXTRACT ( WEEK FROM tt.DAY ) AS week_of_year,
    EXTRACT ( QUARTER FROM tt.DAY ) AS quarter_of_year,
  CASE
      
      WHEN tt.DAY = date_trunc( 'month', tt.DAY + INTERVAL '1 month' ) - INTERVAL '1 day' THEN
      'Y' ELSE'N' 
    END AS is_end_month,
  CASE
      
      WHEN tt.DAY = date_trunc( 'quarter', tt.DAY + INTERVAL '3 month' ) - INTERVAL '1 day' THEN
      'Y' ELSE'N' 
    END AS is_end_quarter,
  CASE
      
      WHEN tt.DAY = date_trunc( 'year', tt.DAY ) + INTERVAL '6 month' - INTERVAL '1 day' THEN
      'Y' ELSE'N' 
    END AS is_end_halfayear,
  CASE
      
      WHEN tt.DAY = date_trunc( 'year', tt.DAY ) + INTERVAL '12 month' - INTERVAL '1 day' THEN
      'Y' ELSE'N' 
    END AS is_end_year,
    'b8617d3d-d2c9-4a2a-93ba-5b2d8b700cb0' AS operator_id,
    'admin' AS operator_name,
    CAST ( CURRENT_DATE AS TIMESTAMP ) AS operator_date,
    NULL AS res_attr1,
    NULL AS res_attr2,
    NULL AS res_attr3,
    NULL AS res_attr4,
  CASE
      
      WHEN EXTRACT ( DOW FROM tt.DAY ) = 6 THEN
      'N' 
      WHEN EXTRACT ( DOW FROM tt.DAY ) = 0 THEN
      'N' ELSE'Y' 
    END AS is_work_day 
  FROM
    (
    SELECT
      generate_series (
        ( SELECT ( date_trunc( 'year', now( ) ) + INTERVAL '1 year' ) :: DATE AS next_year_first_date ),
        ( SELECT ( SELECT ( date_trunc( 'year', now( ) ) + INTERVAL '2 year' ) :: DATE - 1 AS last_year_last_date ) ),
        '1 d' 
      ) AS DAY 
    ) AS tt 
  ) AS A;

After execution, you can see that 365 days of data have been inserted. The only changes here are: '1 year' and 2 year

Actual combat part

In the previous article, we just briefly introduced an application scenario. Here we continue to improve the content of this case. Let's talk about the application scenario. In fact, the requirements are relatively simple, but also common:

• Get the previous working day or the next working day of a day, or get the natural day

Get workday sql

First, we need to get the list of working days of a day according to the current days:

SELECT
    *
FROM
    (
        SELECT
            -ROW_NUMBER ( ) OVER ( ORDER BY T.calendar_date DESC ) AS addDay,
                T.calendar_date,
            T.is_work_day
        FROM
            sa_calendar_table T
        WHERE
            T.calendar_year in (#{nowYear}, #{prevYear})
          and T.calendar_date < CAST ( #{targetYyyyMMdd} AS VARCHAR )

        UNION
        SELECT ROW_NUMBER
                   ( ) OVER ( ORDER BY T.calendar_date )-1 AS addDay,
                T.calendar_date,
               T.is_work_day
        FROM
            sa_calendar_table T
        WHERE
            T.calendar_year in (#{nowYear}, #{prevYear})
          ANd T.calendar_date >= CAST ( #{targetYyyyMMdd} AS VARCHAR )

    ) mm
ORDER BY
    calendar_date

Here we use a practical case to see the form of data:

SELECT
    *
FROM
    (
        SELECT
            -ROW_NUMBER ( ) OVER ( ORDER BY T.calendar_date DESC ) AS addDay,
                T.calendar_date,
            T.is_work_day
        FROM
            sa_calendar_table T
        WHERE
            T.calendar_year in  ('2020', '2021')
          and T.calendar_date < CAST ('2021-12-12' AS VARCHAR )

        UNION
        SELECT ROW_NUMBER
                   ( ) OVER ( ORDER BY T.calendar_date )-1 AS addDay,
                T.calendar_date,
               T.is_work_day
        FROM
            sa_calendar_table T
        WHERE
            T.calendar_year in ('2020', '2021')
          ANd T.calendar_date >= CAST ( '2021-12-12' AS VARCHAR )

    ) mm
ORDER BY
    calendar_date

Seeing this, I believe most readers should know what this is for. Here, we get the current day through 0. If it is + 1, it is the next day, if it is - 1, it is the previous day, and if it is a working day, we judge the data. According to such rules, we can use code to realize the following:

The following is the processing for obtaining the next working day. The code for obtaining the next day is as follows:

 private static final Pattern TD_DAY = Pattern.compile("^(T|D)\\+\\d$");
    private static final String WORK_DAY_CONFIG_T = "T";
    private static final String IS_WORK_DAY = "Y";
    private static final String IS_NOT_WORK_DAY = "N";
    private static final String WORK_DAY_CONFIG_D = "D";


public String findNextDayByCalendarList(CalendarDataProcessBo calendarDataProcessBo) {
        Objects.requireNonNull(calendarDataProcessBo, "The current service transfer object cannot be empty");
        if (StrUtil.isAllNotBlank(new CharSequence[]{calendarDataProcessBo.getBankSettleCycle()}) && !CollectionUtil.isEmpty(calendarDataProcessBo.getCalendarDayDtos())) {
            // Additional days to push forward
            int extDayOfWorkDayCount = calendarDataProcessBo.getExtDayOfWorkDayCount();
            // T+N or D+N
            String bankSettleCycle = calendarDataProcessBo.getBankSettleCycle();
            // Data list corresponding to the screenshot above
            List<SaCalendarDayDto> calendarDayDtos = calendarDataProcessBo.getCalendarDayDtos();
            boolean matches = TD_DAY.matcher(bankSettleCycle).matches();
            // Check regular format
            if (!matches) {
                logger.error("Due to regular expression{}The verification rules are not met{}Therefore, the reconciliation scheduled task cannot process time, and the scheduled task fails to run", bankSettleCycle, TD_DAY);
                throw new UnsupportedOperationException(String.format("Due to regular expression%s The verification rules are not met%s Therefore, the reconciliation scheduled task cannot process time, and the scheduled task fails to run", bankSettleCycle, TD_DAY));
            } else {
                String[] cycDay = bankSettleCycle.split("\\+");
                String tOrDday = cycDay[0];
                String addDay = cycDay[1];
                boolean matchWorkDayEnable;
                if (Objects.equals(tOrDday, "T")) {
                    matchWorkDayEnable = true;
                } else {
                    if (!Objects.equals(tOrDday, "D")) {
                        throw new UnsupportedOperationException("Unable to process t+N perhaps d+N Data other than");
                    }

                    matchWorkDayEnable = false;
                }
                // If you need to get a working day but the next day is not a working day, keep + 1 down
                for(int finDay = Integer.parseInt(addDay) + extDayOfWorkDayCount; finDay < CollectionUtil.size(calendarDayDtos); ++finDay) {
                    Optional<SaCalendarDayDto> first = calendarDayDtos.stream().filter((item) -> {
                        return Objects.equals(item.getAddDay(), String.valueOf(finDay));
                    }).findFirst();
                    if (!first.isPresent()) {
                        throw new UnsupportedOperationException("No working or natural day data were found");
                    }

                    SaCalendarDayDto saCalendarDayDto = (SaCalendarDayDto)first.get();
                    if (!matchWorkDayEnable || !Objects.equals(saCalendarDayDto.getIsWorkDay(), "N")) {
                        return saCalendarDayDto.getCalendarDate();
                    }
                }

                throw new UnsupportedOperationException("No working or natural day data were found");
            }
        } else {
            throw new IllegalArgumentException("Error in passing parameters. Please ensure that all parameters have been passed");
        }
    }

In fact, there are other ways to write here, such as adding a BOOLEAN variable to judge whether to move forward or backward, but individuals do not like to control the behavior of methods in parameters, which is easy to cause problems.

Write at the end

The implementation method of this workday is clumsy and simple. If you have good ideas, welcome to discuss.