欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

创建包含法定节假日、工作日、周末的日历表（mysql、oracle通用）

程序员文章站 2022-05-18 08:01:16
...

获取日历对应的法定节假日的数据
日历数据接口获取参考文章地址：https://blog.csdn.net/u012981882/article/details/112552450

直接访问接口地址：（老老实实按每个月获取下吧，获取全年的测试不成功）

https://api.apihubs.cn/holiday/get?year=2021&month=202112

访问接口结果返回是一个JSON串：

{"code":"0","msg":"ok","data":{"list":[{"year":2021,"month":202112,"date":20211231,"yearweek":202152,"yearday":365,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211128,"lunar_yearday":323,"week":5,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211230,"yearweek":202152,"yearday":364,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211127,"lunar_yearday":322,"week":4,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211229,"yearweek":202152,"yearday":363,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211126,"lunar_yearday":321,"week":3,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211228,"yearweek":202152,"yearday":362,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211125,"lunar_yearday":320,"week":2,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211227,"yearweek":202152,"yearday":361,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211124,"lunar_yearday":319,"week":1,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211226,"yearweek":202151,"yearday":360,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211123,"lunar_yearday":318,"week":7,"weekend":1,"workday":2,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211225,"yearweek":202151,"yearday":359,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211122,"lunar_yearday":317,"week":6,"weekend":1,"workday":2,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211224,"yearweek":202151,"yearday":358,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211121,"lunar_yearday":316,"week":5,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211223,"yearweek":202151,"yearday":357,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211120,"lunar_yearday":315,"week":4,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211222,"yearweek":202151,"yearday":356,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211119,"lunar_yearday":314,"week":3,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211221,"yearweek":202151,"yearday":355,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211118,"lunar_yearday":313,"week":2,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211220,"yearweek":202151,"yearday":354,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211117,"lunar_yearday":312,"week":1,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211219,"yearweek":202150,"yearday":353,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211116,"lunar_yearday":311,"week":7,"weekend":1,"workday":2,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211218,"yearweek":202150,"yearday":352,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211115,"lunar_yearday":310,"week":6,"weekend":1,"workday":2,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211217,"yearweek":202150,"yearday":351,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211114,"lunar_yearday":309,"week":5,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211216,"yearweek":202150,"yearday":350,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211113,"lunar_yearday":308,"week":4,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211215,"yearweek":202150,"yearday":349,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211112,"lunar_yearday":307,"week":3,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211214,"yearweek":202150,"yearday":348,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211111,"lunar_yearday":306,"week":2,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211213,"yearweek":202150,"yearday":347,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211110,"lunar_yearday":305,"week":1,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211212,"yearweek":202149,"yearday":346,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211109,"lunar_yearday":304,"week":7,"weekend":1,"workday":2,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211211,"yearweek":202149,"yearday":345,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211108,"lunar_yearday":303,"week":6,"weekend":1,"workday":2,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211210,"yearweek":202149,"yearday":344,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211107,"lunar_yearday":302,"week":5,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211209,"yearweek":202149,"yearday":343,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211106,"lunar_yearday":301,"week":4,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211208,"yearweek":202149,"yearday":342,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211105,"lunar_yearday":300,"week":3,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211207,"yearweek":202149,"yearday":341,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211104,"lunar_yearday":299,"week":2,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211206,"yearweek":202149,"yearday":340,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211103,"lunar_yearday":298,"week":1,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211205,"yearweek":202148,"yearday":339,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211102,"lunar_yearday":297,"week":7,"weekend":1,"workday":2,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211204,"yearweek":202148,"yearday":338,"lunar_year":2021,"lunar_month":202111,"lunar_date":20211101,"lunar_yearday":296,"week":6,"weekend":1,"workday":2,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211203,"yearweek":202148,"yearday":337,"lunar_year":2021,"lunar_month":202110,"lunar_date":20211029,"lunar_yearday":295,"week":5,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211202,"yearweek":202148,"yearday":336,"lunar_year":2021,"lunar_month":202110,"lunar_date":20211028,"lunar_yearday":294,"week":4,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2},{"year":2021,"month":202112,"date":20211201,"yearweek":202148,"yearday":335,"lunar_year":2021,"lunar_month":202110,"lunar_date":20211027,"lunar_yearday":293,"week":3,"weekend":2,"workday":1,"holiday":10,"holiday_or":10,"holiday_overtime":10,"holiday_today":2,"holiday_legal":2,"holiday_recess":2}],"page":1,"size":31,"total":31}}

根据查询的JSON结果转化成excel，这个直接百度查下JSON转excel工具就可以。

对应的JSON数据表结构（此处为ORACLE）：

create table T_CALENDAR
(
  year             NUMBER(4) not null,
  month            VARCHAR2(6) not null,
  day              VARCHAR2(8) not null,
  yearweek         VARCHAR2(6),
  yearday          NUMBER(3),
  lunar_year       NUMBER(4),
  lunar_month      VARCHAR2(6),
  lunar_date       VARCHAR2(8),
  lunar_yearday    NUMBER(3),
  week             NUMBER(2),
  weekend          NUMBER(1),
  workday          NUMBER(1),
  holiday          NUMBER(3),
  holiday_or       NUMBER(3),
  holiday_overtime NUMBER(3),
  holiday_today    NUMBER(1),
  holiday_legal    NUMBER(1),
  holiday_recess   NUMBER(1)
);

comment on column T_CALENDAR.year
  is '公历年份';
comment on column T_CALENDAR.month
  is '公历月份';
comment on column T_CALENDAR.day
  is '公历日期';
comment on column T_CALENDAR.yearweek
  is '公历一年中的第几周，注意这里的年份是ISO-8601周编号年份，始终以周一至周日为一周。如需获取7天为一周直接使用年份中的天数除7即可。';
comment on column T_CALENDAR.yearday
  is '公历一年中的第几天';
comment on column T_CALENDAR.lunar_year
  is '农历年份';
comment on column T_CALENDAR.lunar_month
  is '农历月份';
comment on column T_CALENDAR.lunar_date
  is '农历日期';
comment on column T_CALENDAR.lunar_yearday
  is '农历一年中的第几天';
comment on column T_CALENDAR.week
  is '星期';
comment on column T_CALENDAR.weekend
  is '是否为周末（星期六和星期日）';
comment on column T_CALENDAR.workday
  is '是否为工作日（包含调休在内需要上班的日子）（1-工作日，2-非工作日）';
comment on column T_CALENDAR.holiday
  is '节假日，这里使用两位数字枚举表示节假日，其中特殊数字10表示非节假日，特殊数字99表示全部节假日';
comment on column T_CALENDAR.holiday_or
  is '其他节假日，枚举与节假日相同，表示同一天中的另一个节日';
comment on column T_CALENDAR.holiday_overtime
  is '节假日调休';
comment on column T_CALENDAR.holiday_today
  is '是否为节日当天';
comment on column T_CALENDAR.holiday_legal
  is '是否为法定节假日（三倍工资）';
comment on column T_CALENDAR.holiday_recess
  is '是否为假期节假日（节日是否放假）';
alter table T_CALENDAR
add constraint PK_T_CALENDAR primary key (DAY);

最后将前面JSON转成的EXCEL日历数据导入到这个表结构中就可以使用了。

2019年至2021年的日历表数据下载：
链接：https://pan.baidu.com/s/11pgKz5WezbVImQynttoRUw
提取码：pxkj

相关标签： 杂项

上一篇： mysql导入数据库错误#1064提示解决方法

下一篇： js中计算两个日期间的工作日（只除去周六日）

推荐阅读