前言

其实这个文章个人之前有进行过发布和讨论，在上一篇文章中，介绍了如何通过postgresql数据库的sql语句构建一个工作日的表，并且介绍如何使用sql语法获取某一天往前或者往后的工作日或者自然日，但是实际阅读之后发现缺少了很多细节，故这里重新梳理一下整个过程，希望可以给读者一个参考。

本次实践只是个人提供的一个工作日获取的解决方案，如果有更好的解决方案欢迎讨论和分享。

上一篇文章链接：juejin.cn/post/702300…

注意使用的数据库为：PostgreSql

前置准备

在介绍具体的编码和处理逻辑之前，我们需要准备表结构和相关的数据。

表设计

首先这里依然先回顾一下这个工作日表获取的表结构：

-- ----------------------------
-- 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 '主键';
COMMENT ON COLUMN "public"."sa_calendar_table"."calendar_year" IS '年';
COMMENT ON COLUMN "public"."sa_calendar_table"."calendar_month" IS '月';
COMMENT ON COLUMN "public"."sa_calendar_table"."calendar_date" IS '日';
COMMENT ON COLUMN "public"."sa_calendar_table"."day_of_week" IS '自然周的第几天';
COMMENT ON COLUMN "public"."sa_calendar_table"."day_of_month" IS '月的第几天';
COMMENT ON COLUMN "public"."sa_calendar_table"."week_of_year" IS '年的第几个自然周';
COMMENT ON COLUMN "public"."sa_calendar_table"."month_of_year" IS '年的第几月';
COMMENT ON COLUMN "public"."sa_calendar_table"."quarter_of_year" IS '年的第几季';
COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_month" IS '是否月末';
COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_quarter" IS '是否季末';
COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_halfayear" IS '是否半年末';
COMMENT ON COLUMN "public"."sa_calendar_table"."is_end_year" IS '是否年末';
COMMENT ON COLUMN "public"."sa_calendar_table"."operator_id" IS '操作人ID';
COMMENT ON COLUMN "public"."sa_calendar_table"."operator_name" IS '操作人名称';
COMMENT ON COLUMN "public"."sa_calendar_table"."operate_date" IS '操作时间';
COMMENT ON COLUMN "public"."sa_calendar_table"."res_attr1" IS '预留字段1';
COMMENT ON COLUMN "public"."sa_calendar_table"."res_attr2" IS '预留字段2';
COMMENT ON COLUMN "public"."sa_calendar_table"."res_attr3" IS '预留字段3';
COMMENT ON COLUMN "public"."sa_calendar_table"."res_attr4" IS '预留字段4';
COMMENT ON COLUMN "public"."sa_calendar_table"."is_work_day" IS '是否为工作日，Y是，N否(即节假日)';

列名称	数据类型	描述	数据长度	不能为空
calendar_id	varchar	主键	255	YES
calendar_year	varchar	年	10	NO
calendar_month	varchar	月	10	NO
calendar_date	varchar	日	10	NO
day_of_week	varchar	自然周的第几天	10	NO
day_of_month	varchar	月的第几天	10	NO
week_of_year	varchar	年的第几个自然周	10	NO
month_of_year	varchar	年的第几月	10	NO
quarter_of_year	varchar	年的第几季	10	NO
is_end_month	varchar	是否月末	10	NO
is_end_quarter	varchar	是否季末	10	NO
is_end_halfayear	varchar	是否半年末	10	NO
is_end_year	varchar	是否年末	10	NO
operator_id	varchar	操作人ID	50	NO
operator_name	varchar	操作人名称	50	NO
operate_date	timestamp	操作时间	6	NO
res_attr1	varchar	预留字段1	40	NO
res_attr2	varchar	预留字段2	40	NO
res_attr3	varchar	预留字段3	40	NO
res_attr4	varchar	预留字段4	40	NO
is_work_day	varchar	是否为工作日，Y是，N否(即节假日)	1	NO

另外这里再教大家一个技巧，如何使用postgresql获取某一个表的表结构：

Postgresql 获取某一个表的表结构：

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 = '这里填表名' 
  AND A.attnum > 0 
  AND A.attrelid = C.oid 
  AND A.atttypid = T.oid 
  AND d.objoid = A.attrelid 
  AND d.objsubid = A.attnum

下面是语句的调用效果，注意上面的语句建议给所有的字段加上注释之后再执行。

网络异常，图片无法展示

填充数据

有了表结构还不够，这里我们还需要填充数据，我们使用如下的sql填充数据内容，sql语句可能略微复杂了些，另外执行过程中可能会出现缺失函数的情况，由于个人使用过程中没有碰到此问题，所以就跳过了：

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;

网络异常，图片无法展示

执行完成之后，可以看到插入了365天的数据，这里唯一需要改动的地方是：'1 year'和2 year

实战部分

在上一篇文章中，只是简单介绍了一个应用场景，这里继续完善此案例的内容，下面来说一下应用的场景，其实需求也比较简单，但是也比较常见：

获取某一天的上一个工作日或者下一个工作日，或者获取自然日

获取工作日sql

首先我们需要根据当前的天数获取某一天的工作日列表：

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

这里我们使用一个实际的案例看一下数据的形式：

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

网络异常，图片无法展示

看到这里，我相信大部分读者应该都知道这是干啥用的了，这里我们通过0获取到当天，如果是+1则是下一天，而如果是-1则是上一天，如果是工作日，则对于数据进行判断，，根据这样的规则，下面我们便可以使用代码来实现：

下面是获取下一天工作日的处理，获取下一天的代码如下：

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, "当前业务传输对象不能为空");
        if (StrUtil.isAllNotBlank(new CharSequence[]{calendarDataProcessBo.getBankSettleCycle()}) && !CollectionUtil.isEmpty(calendarDataProcessBo.getCalendarDayDtos())) {
            // 额外需要往前推的天数
            int extDayOfWorkDayCount = calendarDataProcessBo.getExtDayOfWorkDayCount();
            // T+N 或者 D+N
            String bankSettleCycle = calendarDataProcessBo.getBankSettleCycle();
            // 上方截图对应的数据列表
            List<SaCalendarDayDto> calendarDayDtos = calendarDataProcessBo.getCalendarDayDtos();
            boolean matches = TD_DAY.matcher(bankSettleCycle).matches();
            // 校验正则的格式
            if (!matches) {
                logger.error("由于正则表达式{}不符合校验规则{}所以对账定时任务无法处理时间，定时任务运行失败", bankSettleCycle, TD_DAY);
                throw new UnsupportedOperationException(String.format("由于正则表达式%s不符合校验规则%s所以对账定时任务无法处理时间，定时任务运行失败", 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("无法处理t+N或者d+N以外的数据");
                    }
                    matchWorkDayEnable = false;
                }
        // 如果需要获取工作日但是下一天不是工作日，则不断的+1往下获取
                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("未发现任何工作日或者自然日数据");
                    }
                    SaCalendarDayDto saCalendarDayDto = (SaCalendarDayDto)first.get();
                    if (!matchWorkDayEnable || !Objects.equals(saCalendarDayDto.getIsWorkDay(), "N")) {
                        return saCalendarDayDto.getCalendarDate();
                    }
                }
                throw new UnsupportedOperationException("未发现任何工作日或者自然日数据");
            }
        } else {
            throw new IllegalArgumentException("传递参数有误，请确保所有参数均已传递");
        }
    }

这里其实还有别的写法，比如增加一个BOOLEAN变量判断是往前还是往后，但是个人并不喜欢在参数中控制方法的行为，这样很容易出问题。

写在最后

此工作日的实现方法比较笨拙也比较简单，如果有好的想法欢迎讨论。

获取上下一个工作日实践

前言

前置准备

表设计

填充数据

实战部分

获取工作日sql

写在最后

热门文章

最新文章

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

获取上下一个工作日实践

前言

前置准备

表设计

填充数据

实战部分

获取工作日sql

写在最后

热门文章

最新文章

相关电子书