运行环境：
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 n1
CROSS JOIN num AS n2
CROSS JOIN num AS n3
CROSS JOIN num AS n4
CROSS JOIN num AS n5
ORDER BY 1 ASC
) AS t
Query 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 id
FROM num AS n1
CROSS JOIN num AS n2
CROSS JOIN num AS n3
CROSS JOIN num AS n4
CROSS JOIN num AS n5
ORDER BY 1 ASC
) AS t
WHERE t.id <= 30000
select 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 SELECT
year,
startday
FROM
(
SELECT
'2016' AS year,
CAST('2016-01-04 00:00:00', 'TIMESTAMP') AS startday
UNION ALL
SELECT
'2017',
CAST('2017-01-02 00:00:00', 'TIMESTAMP')
UNION ALL
SELECT
'2018',
CAST('2018-01-01 00:00:00', 'TIMESTAMP')
UNION ALL
SELECT
'2019',
CAST('2019-01-07 00:00:00', 'TIMESTAMP')
UNION ALL
SELECT
'2020',
CAST('2020-01-06 00:00:00', 'TIMESTAMP')
UNION ALL
SELECT
'2021',
CAST('2021-01-04 00:00:00', 'TIMESTAMP')
UNION ALL
SELECT
'2022',
CAST('2022-01-03 00:00:00', 'TIMESTAMP')
) AS t
select * from year_biweek_startday;
SELECT *
FROM year_biweek_startday
Query 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'\G
SELECT *
FROM calender_details
WHERE peroid_date = '20201216'
Query id: 4f687f13-9c06-4f5f-9256-97cfdcc3d2f5
Row 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:00
1 rows in set. Elapsed: 0.005 sec.
2万+
