Get the next working day practice

preface

Personally this article has been published before and discuss, in the previous article, introduces how to use postgresql database table SQL statements to build a working day, and introduces how to use the SQL syntax for one day or the next workday forward or natural, but found the lack of a lot of details, after the actual reading So here to comb through the whole process, I hope to give readers a reference.

This practice is just a solution provided by individuals for one working day. If you have a better solution, welcome to discuss and share.

Link to previous article: juejin.cn/post/702300…

Note The database is PostgreSql

Lead to

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

Table design

First of all, here is a review of the table structure obtained by the workday:

-- ----------------------------
-- 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 'years';
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 "What day of nature 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 'What are the natural weeks 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 quarter of the year?';
COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_month" IS 'End of month';
COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_quarter" IS Is it the end of the season?;
COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_halfayear" IS 'Is it half year?';
COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_year" IS Is it the end of the year?;
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 'Working days, Y yes, N no (i.e. holidays)';

Copy the code

Column name	The data type	describe	The length of the data	Can’t be empty
calendar_id	varchar	A primary key	255	YES
calendar_year	varchar	years	10	NO
calendar_month	varchar	month	10	NO
calendar_date	varchar	day	10	NO
day_of_week	varchar	Day of nature week	10	NO
day_of_month	varchar	The day of the month	10	NO
week_of_year	varchar	The natural week of the year	10	NO
month_of_year	varchar	The month of the year	10	NO
quarter_of_year	varchar	The season of the year	10	NO
is_end_month	varchar	Whether the end of the month	10	NO
is_end_quarter	varchar	Whether the end of the season	10	NO
is_end_halfayear	varchar	Whether the end of the half year	10	NO
is_end_year	varchar	If at the end of	10	NO
operator_id	varchar	Operation of ID	50	NO
operator_name	varchar	Operator Name	50	NO
operate_date	timestamp	Operating time	6	NO
res_attr1	varchar	Reserved Field 1	40	NO
res_attr2	varchar	Reserved Field 2	40	NO
res_attr3	varchar	Reserved Field 3	40	NO
res_attr4	varchar	Reserved Field 4	40	NO
is_work_day	varchar	Whether it is a working day, Y yes, N No (holidays)	1	NO

How to use PostgresQL to get the structure of a table:

Postgresql Retrieves 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
Copy the code

Note that the above statement suggests annotating all fields before executing.

Fill in the data

Table structure is not enough, here we also need to fill the data, we use the following SQL to fill the data content, SQL statement may be slightly complicated, in addition, there may be missing functions in the execution process, because I did not encounter this problem in the process of personal use, so I skip:

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;
Copy the code

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

Actual part

In the last article, only a brief introduction of an application scenario, here to continue to improve the content of this case, the following application scenario, in fact, the requirements are relatively simple, but also relatively common:

Gets the previous or next working day of a day, or gets nature day

Get workdays SQL

First we need to get the list of working days for a particular day based on the current number of 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

Copy the code

Here we use a real example to see what the data looks like:


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
Copy the code

See here, I believe that most readers should know what is this used for, here we through 0 get to the day, if it is + 1 is the next day, and if it is 1 is a day, if it is a working day, is in the judgement for data, and according to these rules, we can use the code below:

The following is the processing to get the next day, the code to get 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, "Current business transfer object cannot be empty");
        if (StrUtil.isAllNotBlank(newCharSequence[]{calendarDataProcessBo.getBankSettleCycle()}) && ! CollectionUtil.isEmpty(calendarDataProcessBo.getCalendarDayDtos())) {// Additional days to be pushed forward
            int extDayOfWorkDayCount = calendarDataProcessBo.getExtDayOfWorkDayCount();
            // T+N 或者 D+N
            String bankSettleCycle = calendarDataProcessBo.getBankSettleCycle();
            // Data list in the above screenshot
            List<SaCalendarDayDto> calendarDayDtos = calendarDataProcessBo.getCalendarDayDtos();
            boolean matches = TD_DAY.matcher(bankSettleCycle).matches();
            // Verifies the format of the re
            if(! matches) { logger.error("Regular expression {} does not comply with the verification rule {}, so the scheduled task cannot process time and fails to run.", bankSettleCycle, TD_DAY);
                throw new UnsupportedOperationException(String.format("Due to the regular expression %s does not comply with the verification rule %s, the scheduled task cannot process time and 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("Can't handle data other than T +N or D +N.");
                    }

                    matchWorkDayEnable = false;
                }
				// If you need to get the day but the next day is not a day, keep +1 down to get it
                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 day or natural day data found.");
                    }

                    SaCalendarDayDto saCalendarDayDto = (SaCalendarDayDto)first.get();
                    if(! matchWorkDayEnable || ! Objects.equals(saCalendarDayDto.getIsWorkDay(),"N")) {
                        returnsaCalendarDayDto.getCalendarDate(); }}throw new UnsupportedOperationException("No working day or natural day data found."); }}else {
            throw new IllegalArgumentException("Error in passing parameters, please make sure all parameters are passed"); }}Copy the code

There are other ways to write this, such as adding a BOOLEAN variable to determine whether the method is forward or backward, but I don’t like the idea of controlling the method’s behavior in parameters, which can lead to problems.

Write in the last

The implementation method for this workday is clunky and simple, so if you have a good idea, please discuss it.

Get the next working day practice

Get the next working day practice

preface

Lead to

Table design

Fill in the data

Actual part

Get workdays SQL

Write in the last

Related Posts

Published by Martian 3.2.18, FastJson is deprecated

How does Go-Zero deal with the massive amount of timed/delayed tasks? Technical essay | 🏆 nuggets – double festival special articles

The income tax threshold will rise to 5000. Use Python to calculate how much less tax you will pay