Small knowledge, big challenge! This paper is participating in theEssentials for programmers”Creative activities

preface

Weekday access or common, such as Banks and cross-border electricity trading, sometimes in the education sector may also be useful to, because we don’t know that day is a holiday, every year sometimes involves the paid leave and so on and to work more troublesome, so here just for a record, after using can directly bring table for your reference, I hope this note will be helpful to the reader.

Note that this article uses a PostgreSql database. Using other databases requires minor changes to the SQL content.

Date table design

Date table design is not fixed, the following is for reference.

Data entry template

Of course, it is still used for PostgreSql database, the following uses the SQL database into the database, after the database can see the data content of a year.

The following select INSERT statement is used to import data into the database, but we need to build the related data table from the above data table

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

Example: Get a few days ago business day

For personal use is not very experienced, so here record personal recently reached a use case, the following case is to use the row_number () to obtain how many days before working days, according to the following SQL statement we can easily complete working days before the delivery date of how many, the default will be in accordance with the current date as a watershed. Negative if the date is before the current day, positive if the date is after the current day.

select * from 
(
select - ROW_NUMBER(a)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' 
union
select ROW_NUMBER(a)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
Copy the code

The running effect is not shown here, but the previous day is -1, the next day is +1 and so on.

Problem to collect

ERROR: function gen_random_uuid() does not exist

CREATE EXTENSION pgcrypto;
Copy the code

A postgresQL error similar to the following is reported:

# SELECT gen_random_uuid();
ERROR:  function gen_random_uuid() does not exist
LINE 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-a57a4 -f1e-8e66- 152.be08e6165
(1 row)
Copy the code

conclusion

Is a simple summary, I hope to help readers

Write in the last

This time is mainly a personal time record.