pg 生成节假日

## 自然工作日和周末

```sql
select to_char(dt,'YYYY-MM-DD') "日期",
    to_char(dt,'ID') "周几",  --周一-1
    to_char(dt,'DDD') "一年中第几天",
    to_char(dt,'Q') "季度",
    case when to_char(dt,'ID')::int % 7 in (0,6) then '周末' else '工作日' end "类型"
from generate_series('2023-01-01'::timestamp,
                     '2023-12-31'::timestamp,
                     interval '1 day') as dt
;
```

## 国务院节假日安排

2023年节假日参考：https://www.gov.cn/fuwu/2022-12/08/content_5730853.htm

```sql
select to_char(dt,'YYYY-MM-DD') "日期",
    to_char(dt,'ID') "周几",  --周一-1
    to_char(dt,'DDD') "一年中第几天",
    to_char(dt,'Q') "季度",
    type "类型"
from (values
    ('2023-01-01'::timestamp,'节假日'),
    ('2023-01-02'::timestamp,'节假日'),
    ('2023-01-21'::timestamp,'节假日'),
    ('2023-01-22'::timestamp,'节假日'),
    ('2023-01-23'::timestamp,'节假日'),
    ('2023-01-24'::timestamp,'节假日'),
    ('2023-01-25'::timestamp,'节假日'),
    ('2023-01-26'::timestamp,'节假日'),
    ('2023-01-27'::timestamp,'节假日'),
    ('2023-01-28'::timestamp,'上班'),
    ('2023-01-29'::timestamp,'上班'),
    ('2023-04-05'::timestamp,'节假日'),
    ('2023-04-23'::timestamp,'上班'),
    ('2023-04-29'::timestamp,'节假日'),
    ('2023-04-30'::timestamp,'节假日'),
    ('2023-05-01'::timestamp,'节假日'),
    ('2023-05-02'::timestamp,'节假日'),
    ('2023-05-03'::timestamp,'节假日'),
    ('2023-05-06'::timestamp,'上班'),
    ('2023-06-22'::timestamp,'节假日'),
    ('2023-06-23'::timestamp,'节假日'),
    ('2023-06-24'::timestamp,'节假日'),
    ('2023-06-25'::timestamp,'上班'),
    ('2023-09-29'::timestamp,'节假日'),
    ('2023-09-30'::timestamp,'节假日'),
    ('2023-10-01'::timestamp,'节假日'),
    ('2023-10-02'::timestamp,'节假日'),
    ('2023-10-03'::timestamp,'节假日'),
    ('2023-10-04'::timestamp,'节假日'),
    ('2023-10-05'::timestamp,'节假日'),
    ('2023-10-06'::timestamp,'节假日'),
    ('2023-10-07'::timestamp,'上班'),
    ('2023-10-08'::timestamp,'上班')
) t(dt,type)
```

## 2023年节假日

```sql
with t1 as (
    select to_char(dt,'YYYY-MM-DD') "日期",
        to_char(dt,'ID') "周几",  --周一-1
        to_char(dt,'DDD') "一年中第几天",
        to_char(dt,'Q') "季度",
        case when to_char(dt,'ID')::int % 7 in (0,6) then '周末' else '工作日' end "类型"
    from generate_series('2023-01-01'::timestamp,
                         '2023-12-31'::timestamp,
                         interval '1 day') as dt
),
t2 as (
    select to_char(dt,'YYYY-MM-DD') "日期",
        to_char(dt,'ID') "周几",  --周一-1
        to_char(dt,'DDD') "一年中第几天",
        to_char(dt,'Q') "季度",
        type "类型"
    from (values
        ('2023-01-01'::timestamp,'节假日'),
        ('2023-01-02'::timestamp,'节假日'),
        ('2023-01-21'::timestamp,'节假日'),
        ('2023-02-22'::timestamp,'节假日'),
        ('2023-01-23'::timestamp,'节假日'),
        ('2023-01-24'::timestamp,'节假日'),
        ('2023-01-25'::timestamp,'节假日'),
        ('2023-01-26'::timestamp,'节假日'),
        ('2023-01-27'::timestamp,'节假日'),
        ('2023-01-28'::timestamp,'上班'),
        ('2023-01-29'::timestamp,'上班'),
        ('2023-04-05'::timestamp,'节假日'),
        ('2023-04-23'::timestamp,'上班'),
        ('2023-04-29'::timestamp,'节假日'),
        ('2023-04-30'::timestamp,'节假日'),
        ('2023-05-01'::timestamp,'节假日'),
        ('2023-05-02'::timestamp,'节假日'),
        ('2023-05-03'::timestamp,'节假日'),
        ('2023-05-06'::timestamp,'上班'),
        ('2023-06-22'::timestamp,'节假日'),
        ('2023-06-23'::timestamp,'节假日'),
        ('2023-06-24'::timestamp,'节假日'),
        ('2023-06-25'::timestamp,'上班'),
        ('2023-09-29'::timestamp,'节假日'),
        ('2023-09-30'::timestamp,'节假日'),
        ('2023-10-01'::timestamp,'节假日'),
        ('2023-10-02'::timestamp,'节假日'),
        ('2023-10-03'::timestamp,'节假日'),
        ('2023-10-04'::timestamp,'节假日'),
        ('2023-10-05'::timestamp,'节假日'),
        ('2023-10-06'::timestamp,'节假日'),
        ('2023-10-07'::timestamp,'上班'),
        ('2023-10-08'::timestamp,'上班')
    ) t(dt,type)
)

select *
from t2
union all
select *
from t1
where "日期" not in (select "日期" from t2)
order by "日期"
;
```