运行环境：
SELECT version()Query id: 90d74a1e-3ce1-42b4-8b66-bd09802310c4┌─version()─┐
│ 20.12.3.3 │
└───────────┘1 rows in set. Elapsed: 0.002 sec.
clickhouse的 date和datetime的时间范围目前只支持 [1970-01-01 00:00:00, 2105-12-31 23:59:59].
计算可以容纳的最大天数：
select dateDiff('day',cast('1970-01-01' as timestamp),cast('2105-12-31' as timestamp)) gap_day;┌─gap_day─┐
│ 49672 │
└─────────┘1 rows in set. Elapsed: 0.008 sec.SELECT count(1)
FROM
(SELECT (((n1.i + (n2.i * 10)) + (n3.i * 100)) + (n4.i * 1000)) + (n5.i * 10000)FROM num AS n1CROSS JOIN num AS n2CROSS JOIN num AS n3CROSS JOIN num AS n4CROSS JOIN num AS n5ORDER BY 1 ASC
) AS tQuery id: 8e6eba23-d993-496a-8167-23fbe71e6d22┌─count(1)─┐
│ 100000 │
└──────────┘1 rows in set. Elapsed: 0.011 sec.创建表calender ，一个字段用于存储日期即可。
create database calender;
use calender;create table num(i int) ENGINE = MergeTree() order by i;insert into num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);create table calender(date_dt timestamp) engine=MergeTree() order by date_dt;INSERT INTO calender (date_dt) SELECT addDays(CAST('2016-01-01 00:00:00', 'timestamp'), t.id) AS date_dt
FROM
(SELECT (((n1.i + (n2.i * 10)) + (n3.i * 100)) + (n4.i * 1000)) + (n5.i * 10000) AS idFROM num AS n1CROSS JOIN num AS n2CROSS JOIN num AS n3CROSS JOIN num AS n4CROSS JOIN num AS n5ORDER BY 1 ASC
) AS t
WHERE t.id <= 30000select min(date_dt),max(date_dt) ,count(date_dt) from calender;┌────────min(date_dt)─┬────────max(date_dt)─┬─count(date_dt)─┐
│ 2016-01-01 00:00:00 │ 2098-02-19 00:00:00 │ 30001 │
└─────────────────────┴─────────────────────┴────────────────┘1 rows in set. Elapsed: 0.008 sec. Processed 30.00 thousand rows, 120.00 KB (3.98 million rows/s., 15.92 MB/s.)create table year_biweek_startday( year String ,startday timestamp) engine=MergeTree() order by year;INSERT INTO year_biweek_startday SELECTyear,startday
FROM
(SELECT'2016' AS year,CAST('2016-01-04 00:00:00', 'TIMESTAMP') AS startdayUNION ALLSELECT'2017',CAST('2017-01-02 00:00:00', 'TIMESTAMP')UNION ALLSELECT'2018',CAST('2018-01-01 00:00:00', 'TIMESTAMP')UNION ALLSELECT'2019',CAST('2019-01-07 00:00:00', 'TIMESTAMP')UNION ALLSELECT'2020',CAST('2020-01-06 00:00:00', 'TIMESTAMP')UNION ALLSELECT'2021',CAST('2021-01-04 00:00:00', 'TIMESTAMP')UNION ALLSELECT'2022',CAST('2022-01-03 00:00:00', 'TIMESTAMP')
) AS tselect * from year_biweek_startday;SELECT *
FROM year_biweek_startdayQuery id: 4a95335d-a8af-45f2-925a-0883d4b57eb6┌─year─┬────────────startday─┐
│ 2016 │ 2016-01-04 00:00:00 │
│ 2017 │ 2017-01-02 00:00:00 │
│ 2018 │ 2018-01-01 00:00:00 │
│ 2019 │ 2019-01-07 00:00:00 │
│ 2020 │ 2020-01-06 00:00:00 │
│ 2021 │ 2021-01-04 00:00:00 │
│ 2022 │ 2022-01-03 00:00:00 │
└──────┴─────────────────────┘7 rows in set. Elapsed: 0.004 sec.create table calender_details(date_dt timestamp comment '日期 2016-01-01 00:00:00.0 timestamp'
,peroid_date String COMMENT '日期,20201104'
,peroid_month String COMMENT '月份,202009'
,period_quarter String comment '季度,202001'
,year_day int comment '自然年的第几天，范围1-366'
,year_week int comment '自然年的第几周，范围1-53'
,year_month int comment '自然年的第几月，范围1-12'
,year_quarter int comment '自然年的第几季，范围1-4'
,year int comment '自然年,范围0001-9999'
,quarter_day int comment '一个季度的第几天，范围1-93'
,quarter_week int comment '一个季度的第几周，范围1-13'
,quarter_month int comment '一个季度的第几月，范围1-3'
,month_day int comment '一个月的第几天，范围1-31'
,month_week int comment '一个月的第几周，范围1-5'
,month_name_en String comment '自然月的英文全称 January--December'
,month_name_en_abbr String comment '自然月的英文简写 Jan--Dec'
,week_day int comment '周几 1--7'
,week_name_en String comment '周几的英文全称 Monday--Sunday '
,week_name_en_abbr String comment '周几的英文简写 Mon--Sun'
,biweek int comment '自然年的第几个双周 1-26'
,biweek_startday timestamp comment '双周的开始日期'
,biweek_endday timestamp comment '双周的结束日期'
) engine=MergeTree()
partition by year
order by peroid_date;
查看双周数据的测试：select c.date_dt,s.year,s.startday,dateDiff('day',s.startday,c.date_dt) diff_day, dateDiff('day',s.startday,c.date_dt)/7 bi_day, floor(datediff('day',s.startday,c.date_dt)/14)+1 biweek,addDays(s.startday,cast(floor(dateDiff('day',s.startday,c.date_dt)/14) as int)*14) biweek_startday,addDays(s.startday,cast(floor(dateDiff('day',s.startday,c.date_dt)/14) as int)*14+13) biweek_endtday
from calender c inner join year_biweek_startday s on toYear(c.date_dt)=cast(s.year as UInt16)
where toYear(c.date_dt)=2020
order by 1
最终生成日历的脚本：
insert into calender_details(
date_dt
,peroid_date
,peroid_month
,period_quarter
,year_day
,year_week
,year_month
,year_quarter
,year
,quarter_day
,quarter_week
,quarter_month
,month_day
,month_week
,month_name_en
,month_name_en_abbr
,week_day
,week_name_en
,week_name_en_abbr
,biweek
,biweek_startday
,biweek_endday
)
select c.date_dt,toYYYYMMDD(date_dt) period_date,toYYYYMM(date_dt) period_month,concat(cast(toYear(date_dt ) as String),'0',cast(toQuarter(date_dt) as String )) period_quarter,toDayOfYear(date_dt) year_day,toWeek(date_dt) year_week,toMonth(date_dt) year_month ,toQuarter(date_dt) year_quarter,toYear(date_dt) year,dateDiff('day',toStartOfQuarter(date_dt) ,date_dt) +1 quarter_day,dateDiff('week',toStartOfQuarter(date_dt),date_dt )+1 quarter_week,dateDiff('month',toStartOfQuarter(date_dt),date_dt )+1 quarter_month,toDayOfYear(date_dt) month_day,dateDiff('week',toStartOfMonth(date_dt),date_dt)+1 month_week,case when toMonth(date_dt)= 1 then 'January' when toMonth(date_dt)= 2 then 'February' when toMonth(date_dt)= 3 then 'March' when toMonth(date_dt)= 4 then 'April' when toMonth(date_dt)= 5 then 'May' when toMonth(date_dt)= 6 then 'June' when toMonth(date_dt)= 7 then 'July' when toMonth(date_dt)= 8 then 'August' when toMonth(date_dt)= 9 then 'September' when toMonth(date_dt)= 10 then 'October' when toMonth(date_dt)= 11 then 'November' when toMonth(date_dt)=12 then 'December' end month_name_en
,case when toMonth(date_dt)= 1 then 'Jan'when toMonth(date_dt)=2 then 'Feb'when toMonth(date_dt)=3 then 'Mar'when toMonth(date_dt)=4 then 'Apr'when toMonth(date_dt)=5 then 'May'when toMonth(date_dt)=6 then 'Jun'when toMonth(date_dt)=7 then 'Jul'when toMonth(date_dt)=8 then 'Aug'when toMonth(date_dt)=9 then 'Sep'when toMonth(date_dt)=10 then 'Oct'when toMonth(date_dt)=11 then 'Nov'when toMonth(date_dt)=12 then 'Dec'end month_name_en_abbr
,toDayOfWeek(date_dt) week_day
,case when toDayOfWeek(date_dt)=1 then 'Monday' when toDayOfWeek(date_dt)=2 then 'Tuesday' when toDayOfWeek(date_dt)=3 then 'Wednesday' when toDayOfWeek(date_dt)=4 then 'Thursday' when toDayOfWeek(date_dt)=5 then 'Friday' when toDayOfWeek(date_dt)=6 then 'Saturday' when toDayOfWeek(date_dt)=7 then 'Sunday' end week_name_en,case when toDayOfWeek(date_dt)= 1 then 'Mon'when toDayOfWeek(date_dt)=2 then 'Tue'when toDayOfWeek(date_dt)=3 then 'Wed'when toDayOfWeek(date_dt)=4 then 'Thu'when toDayOfWeek(date_dt)=5 then 'Fri'when toDayOfWeek(date_dt)=6 then 'Sat'when toDayOfWeek(date_dt)=7 then 'Sun'end week_name_en_abbr , floor(datediff('day',s.startday,c.date_dt)/14)+1 biweek,addDays(s.startday,cast(floor(dateDiff('day',s.startday,c.date_dt)/14) as int)*14) biweek_startday,addDays(s.startday,cast(floor(dateDiff('day',s.startday,c.date_dt)/14) as int)*14+13) biweek_endtday
from calender c inner join year_biweek_startday s on toYear(c.date_dt)=cast(s.year as UInt16)
where toYear(c.date_dt) between 2016 and 2022
order by 1查询验证：
select * from calender_details where peroid_date ='20201216'\GSELECT *
FROM calender_details
WHERE peroid_date = '20201216'Query id: 4f687f13-9c06-4f5f-9256-97cfdcc3d2f5Row 1:
──────
date_dt: 2020-12-16 00:00:00
peroid_date: 20201216
peroid_month: 202012
period_quarter: 202004
year_day: 351
year_week: 50
year_month: 12
year_quarter: 4
year: 2020
quarter_day: 77
quarter_week: 12
quarter_month: 3
month_day: 351
month_week: 3
month_name_en: December
month_name_en_abbr: Dec
week_day: 3
week_name_en: Wednesday
week_name_en_abbr: Wed
biweek: 25
biweek_startday: 2020-12-07 00:00:00
biweek_endday: 2020-12-20 00:00:001 rows in set. Elapsed: 0.005 sec.
