一次工作日的获取技巧记录

前言

工作日的获取还是比拟常见的，比方银行和跨境电商的交易，有时候在教育的行业也会有用到，因为咱们不晓得每一年那一天是节假日，有时候波及调休等等又要下班比拟麻烦，所以这里罗唆做一次记录，当前用到的时候能够间接把表拿来做参考，心愿这次的笔记能对读者有所帮忙。

留神，本文应用的是PostgreSql数据库，应用其余的数据库须要稍微的批改sql的内容。

日期表的设计

日期表的设计不是固定的，上面的内容其实都是供参考应用。

数据入库模板

当然还是针对PostgreSql数据库应用，上面应用了数据库入库的sql，入库之后能够看到一年的数据内容。

上面的select insert语句来讲数据入库，当然咱们须要先依据下面的数据表构建相干的数据表才行

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;

案例：获取几天前工作日

因为集体应用的教训也不是非常丰盛，所以这里记录集体最近接触到的一个应用案例，上面的案例是应用row_number()来获取多少个日之前的工作日，依据上面的sql语句咱们能够轻松实现多少个日之前的工作日，默认会依照以后的日期作为分水岭，如果在当天之前的日期，则为正数，如果是以后天数之后的内容，则为负数。

select * from (select - ROW_NUMBER () OVER (ORDER BY t.calendar_date desc) as add_day,t.calendar_date from sa_calendar_table t where t.calendar_year = '2021' and t.calendar_date < cast(CURRENT_DATE as VARCHAR) and t.is_work_day = 'Y' unionselect ROW_NUMBER () OVER (ORDER BY t.calendar_date) -1 as add_day,t.calendar_date from sa_calendar_table t where t.calendar_year = '2021' and t.calendar_date >= cast(CURRENT_DATE as VARCHAR) and t.is_work_day = 'Y' ) mm ORDER BY calendar_date

运行成果这里就不进行展现了，无非就是前一天工作日为-1，后一天工作日为+1等等的内容。

问题收集

如果呈现ERROR: function gen_random_uuid() does not exist的代码，能够执行上面的sql语句实现。

CREATE EXTENSION pgcrypto;

如果再postgresql中会有如下的相似报错：

# SELECT gen_random_uuid();ERROR:  function gen_random_uuid() does not existLINE 1: select gen_random_uuid();               ^HINT:  No function matches the given name and argument types. You might need to add explicit type casts.# CREATE EXTENSION pgcrypto;CREATE EXTENSION# SELECT gen_random_uuid();           gen_random_uuid            -------------------------------------- 19a12b49-a57a-4f1e-8e66-152be08e6165(1 row)

总结

算是一次简略的总结，心愿对于读者有帮忙

写在最初

本次次要为一次集体的工夫记录。