ClickHouse函数 3.时间函数

olap clickhouse
clickhouse
发布日期:   2021-08-25

转换格式

1
2
3
4
5
6
7
8
9
SELECT
'2021-08-21 17:34:00' AS timestamp,
CAST(timestamp AS DateTime) AS datetime,
CAST(timestamp AS Date) AS date,
CAST(timestamp, 'String') AS string,
CAST(timestamp, 'FixedString(22)') AS fixed_string;
┌─timestamp───────────┬────────────datetime─┬───────date─┬─string──────────────┬─fixed_string────────┐
│ 2021-08-21 17:34:00 │ 2021-08-21 17:34:00 │ 2021-08-21 │ 2021-08-21 17:34:00 │ 2021-08-21 17:34:00 │
└─────────────────────┴─────────────────────┴────────────┴─────────────────────┴─────────────────────┘

时间计算

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
WITH toDateTime('2021-08-21 17:34:00') AS dateTime,
INTERVAL 1 HOUR AS interval_hour,
toIntervalMinute(1) AS interval_to_minute,
toIntervalMonth(1) AS interval_to_second
SELECT
dateTime + interval_hour,
dateTime + interval_to_minute,
dateTime + interval_to_second;
┌─plus(dateTime, interval_hour)─┬─plus(dateTime, interval_to_minute)─┬─plus(dateTime, interval_to_second)─┐
│           2021-08-21 18:34:00 │                2021-08-21 17:35:00 │                2021-09-21 17:34:00 │
└───────────────────────────────┴────────────────────────────────────┴────────────────────────────────────┘

-- 同

select 
toDateTime('2021-08-21 17:34:00') AS dateTime,
dateTime + INTERVAL 1 HOUR AS interval_hour,
dateTime + toIntervalMinute(1) AS interval_to_minute,
dateTime + toIntervalSecond(1) AS interval_to_second;
┌────────────dateTime─┬───────interval_hour─┬──interval_to_minute─┬──interval_to_second─┐
│ 2021-08-21 17:34:00 │ 2021-08-21 18:34:00 │ 2021-08-21 17:35:00 │ 2021-08-21 17:34:01 │
└─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┘

-- 同

WITH
toDateTime('2021-08-21 17:34:00') as datetime,
INTERVAL 1 HOUR AS interval_hour,
toIntervalMinute(1) as interval_to_minure,
toIntervalSecond(1) as interval_to_second
SELECT
plus(datetime, interval_hour),
plus(datetime, interval_to_minure),
plus(datetime, interval_to_second);
┌─plus(datetime, interval_hour)─┬─plus(datetime, interval_to_minure)─┬─plus(datetime, interval_to_second)─┐
│           2021-08-21 18:34:00 │                2021-08-21 17:35:00 │                2021-08-21 17:34:01 │
└───────────────────────────────┴────────────────────────────────────┴────────────────────────────────────┘

日期计算

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH toDate('2021-08-21') AS date,
INTERVAL 1 WEEK AS interval_week,
toIntervalWeek(1) AS interval_to_week,
toIntervalMonth(1) AS interval_to_month
SELECT
date + interval_week,
date + interval_to_week,
date + interval_to_month;
┌─plus(date, interval_week)─┬─plus(date, interval_to_week)─┬─plus(date, interval_to_month)─┐
│                2021-08-28 │                   2021-08-28 │                    2021-09-21 │
└───────────────────────────┴──────────────────────────────┴───────────────────────────────┘

-- 同

select 
toDate('2021-08-21') AS date,
date + INTERVAL 1 WEEK AS interval_week,
date + toIntervalWeek(1) AS interval_to_week,
date + toIntervalMonth(1) AS interval_to_month;
┌───────date─┬─interval_week─┬─interval_to_week─┬─interval_to_month─┐
│ 2021-08-21 │    2021-08-28 │       2021-08-28 │        2021-09-21 │
└────────────┴───────────────┴──────────────────┴───────────────────┘

toUnixTimestamp

  • toUnixTimestamp(DateTime)
  • 将DateTime转换成Unix时间戳
1
2
3
4
5
6

SELECT toDateTime('2021-08-21 17:34:00') AS time,
toUnixTimestamp(time) as unixTimestamp;
┌────────────────time─┬─unixTimestamp─┐
│ 2021-08-21 17:34:00 │    1629538440 │
└─────────────────────┴───────────────┘

now

1
2
3
4
select now();
┌───────────────now()─┐
│ 2021-08-21 17:34:00 │
└─────────────────────┘

toDate

1
2
3
4
5
6
7
8
9
10
11
-- 保留 时-分-秒
select toDateTime('2021-08-21 17:34:00') AS time, toDate(time) as date_local;
┌────────────────time─┬─date_local─┐
│ 2021-08-21 17:34:00 │ 2021-08-21 │
└─────────────────────┴────────────┘

select toDateTime('2021-08-21 17:34:00') AS time, toDate(time, 'Asia/Shanghai') AS date_shanghai;
┌────────────────time─┬─date_shanghai─┐
│ 2021-08-21 17:34:00 │    2021-08-21 │
└─────────────────────┴───────────────┘

today

1
2
3
4
5
-- 其功能与'toDate(now())'相同
SELECT toDateTime('2021-08-21 17:34:00') as time,today() AS cur_dateTime2;
┌────────────────time─┬─cur_dateTime2─┐
│ 2021-08-21 17:34:00 │    2021-09-17 │
└─────────────────────┴───────────────┘

toTime

1
2
3
4
5
-- 将DateTime中的日期转换为一个固定的日期，同时保留时间部分。
select toDateTime('2021-08-21 17:34:00') AS time,toTime(time) as date_time;
┌────────────────time─┬───────────date_time─┐
│ 2021-08-21 17:34:00 │ 1970-01-02 17:34:00 │
└─────────────────────┴─────────────────────┘

toYear

1
2
3
4
5
-- 获取年份
select toDateTime('2021-08-21 17:34:00') AS time,toYear(time) as get_year;
┌────────────────time─┬─get_year─┐
│ 2021-08-21 17:34:00 │     2021 │
└─────────────────────┴──────────┘

toMonth

1
2
3
4
5
-- 获取月份
select toDateTime('2021-08-21 17:34:00') AS time,toMonth(time) as get_month;
┌────────────────time─┬─get_month─┐
│ 2021-08-21 17:34:00 │         8 │
└─────────────────────┴───────────┘

toQuarter

1
2
3
4
5
-- 一年分为四个季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）
select toDateTime('2021-08-21 17:34:00') AS time,toQuarter(time) as get_quarter;
┌────────────────time─┬─get_quarter─┐
│ 2021-08-21 17:34:00 │           3 │
└─────────────────────┴─────────────┘

toHour

1
2
3
4
select toDateTime('2021-08-21 17:34:00') AS time,toHour(time) as get_hour;
┌────────────────time─┬─get_hour─┐
│ 2021-08-21 17:34:00 │       17 │
└─────────────────────┴──────────┘

toMinute

1
2
3
4
select toDateTime('2021-08-21 17:34:00') AS time,toMinute(time) as get_minute;
┌────────────────time─┬─get_minute─┐
│ 2021-08-21 17:34:00 │         34 │
└─────────────────────┴────────────┘

toSecond

1
2
3
4
select toDateTime('2021-08-21 17:34:00') AS time,toSecond(time) as get_second;
┌────────────────time─┬─get_second─┐
│ 2021-08-21 17:34:00 │          0 │
└─────────────────────┴────────────┘

toDayOfYear

1
2
3
4
5
6
7
8
-- 获取 DateTime中的当前日期是当前年份的第几天，当前月份的第几日，当前星期的周几
-- 当前年份中的第几天
select toDateTime('2021-08-21 17:34:00') AS time,toDayOfYear(time);
┌────────────────time─┬─toDayOfYear(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                                            233 │
└─────────────────────┴────────────────────────────────────────────────┘


toDayOfMonth

1
2
3
4
5
6
-- 当前月份的第几天
select toDateTime('2021-08-21 17:34:00') AS time,toDayOfMonth(time);
┌────────────────time─┬─toDayOfMonth(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                                              21 │
└─────────────────────┴─────────────────────────────────────────────────┘

toDayOfWeek

1
2
3
4
5
6
-- 星期
select toDateTime('2021-08-21 17:34:00') AS time,toDayOfWeek(time);
┌────────────────time─┬─toDayOfWeek(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                                              6 │
└─────────────────────┴────────────────────────────────────────────────┘

toDateTime

1
2
3
4
5
6
select toDateTime('2021-08-21 17:34:00') AS time,toDateTime(time, 'Asia/Shanghai') AS time_shanghai;
┌────────────────time─┬───────time_shanghai─┐
│ 2021-08-21 17:34:00 │ 2021-08-21 17:34:00 │
└─────────────────────┴─────────────────────┘


toStartOfYear

1
2
3
4
5
6
-- 得到当前年份的第一天,当前月份的第一天，当前季度的第一天，当前日期的开始时刻
select toDateTime('2021-08-21 17:34:00') AS time,toStartOfYear(time);
┌────────────────time─┬─toStartOfYear(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                                       2021-01-01 │
└─────────────────────┴──────────────────────────────────────────────────┘

toStartOfMonth

1
2
3
4
5
6
select toDateTime('2021-08-21 17:34:00') AS time,toStartOfMonth(time);
┌────────────────time─┬─toStartOfMonth(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                                        2021-08-01 │
└─────────────────────┴───────────────────────────────────────────────────┘


toStartOfQuarter

1
2
3
4
5
select toDateTime('2021-08-21 17:34:00') AS time,toStartOfQuarter(time);
┌────────────────time─┬─toStartOfQuarter(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                                          2021-07-01 │
└─────────────────────┴─────────────────────────────────────────────────────┘

toStartOfDay

1
2
3
4
5
select toDateTime('2021-08-21 17:34:00') AS time,toStartOfDay(time);
┌────────────────time─┬─toStartOfDay(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                             2021-08-21 00:00:00 │
└─────────────────────┴─────────────────────────────────────────────────┘

toStartOfHour

1
2
3
4
5
select toDateTime('2021-08-21 17:34:00') AS time,toStartOfHour(time);
┌────────────────time─┬─toStartOfHour(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                              2021-08-21 17:00:00 │
└─────────────────────┴──────────────────────────────────────────────────┘

toStartOfMinute

1
2
3
4
5
select toDateTime('2021-08-21 17:34:00') AS time,toStartOfMinute(time) ;
┌────────────────time─┬─toStartOfMinute(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                                2021-08-21 17:34:00 │
└─────────────────────┴────────────────────────────────────────────────────┘

toRelativeYearNum

1
2
3
4
5
6
7
-- 从过去的某个固定的时间开始，以此得到当前指定的日期的编号
select toDateTime('2021-08-21 17:34:00') AS time,toRelativeYearNum(time);
┌────────────────time─┬─toRelativeYearNum(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                                                 2021 │
└─────────────────────┴──────────────────────────────────────────────────────┘


toRelativeQuarterNum

1
2
3
4
5
select toDateTime('2021-08-21 17:34:00') AS time,toRelativeQuarterNum(time);
┌────────────────time─┬─toRelativeQuarterNum(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                                                    8086 │
└─────────────────────┴─────────────────────────────────────────────────────────┘

toISOYear

1
2
3
4
5
SELECT toDateTime('2021-08-21 17:34:00') as time,toISOYear(time) AS iso_year;
┌────────────────time─┬─iso_year─┐
│ 2021-08-21 17:34:00 │     2021 │
└─────────────────────┴──────────┘

toISOWeek

1
2
3
4
5
SELECT toDateTime('2021-08-21 17:34:00') as time,toISOWeek(time) AS iso_week;
┌────────────────time─┬─iso_week─┐
│ 2021-08-21 17:34:00 │       33 │
└─────────────────────┴──────────┘

yesterday

1
2
3
4
5
6
-- 当前日期的上一天
SELECT yesterday() AS yesterday;
┌──yesterday─┐
│ 2021-08-20 │
└────────────┘

toYYYYMMDDhhmmss

1
2
3
4
5
6
-- 目前只有这三种格式，没有什么toYYYY(),toYYYddmm()之类的函数，不要想当然。
SELECT toDateTime('2021-08-21 17:34:00') as time,toYYYYMMDDhhmmss(time);
┌────────────────time─┬─toYYYYMMDDhhmmss(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                                      20210821173400 │
└─────────────────────┴─────────────────────────────────────────────────────┘

toYYYYMMDD

1
2
3
4
SELECT toDateTime('2021-08-21 17:34:00') as time,toYYYYMMDD(time);
┌────────────────time─┬─toYYYYMMDD(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                                      20210821 │
└─────────────────────┴───────────────────────────────────────────────┘

toYYYYMM

1
2
3
4
SELECT toDateTime('2021-08-21 17:34:00') as time,toYYYYMM(time);
┌────────────────time─┬─toYYYYMM(toDateTime('2021-08-21 17:34:00'))─┐
│ 2021-08-21 17:34:00 │                                      202108 │
└─────────────────────┴─────────────────────────────────────────────┘

formatDateTime

  • formatDateTime(Time, Format[,Timezone])
  • 函数引用
表达式 含义
%F 抽取日期部分
%T 抽取时间部分
%Y 抽取年份
%y 抽取年份后两位，(00-99)
%m 抽取月
%d 抽取 日
%H 抽取 时
%I 抽取 时，转为 12 小时制
%M 抽取 分 部分
%S 抽取 秒 部分
%w 判断日期为周几
%D 抽取日期，转为 日/月/年 的格式
%p 判断时间是上午还是下午 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
SELECT toDateTime('2021-08-21 17:34:00') as time,formatDateTime(time, '%F') AS def_datetime_get_date;
┌────────────────time─┬─def_datetime_get_date─┐
│ 2021-08-21 17:34:00 │ 2021-08-21            │
└─────────────────────┴───────────────────────┘

SELECT toDateTime('2021-08-21 17:34:00') as time,formatDateTime(time, '%T') AS def_datetime_get_time;
┌────────────────time─┬─def_datetime_get_time─┐
│ 2021-08-21 17:34:00 │ 17:34:00              │
└─────────────────────┴───────────────────────┘

SELECT toDateTime('2021-08-21 17:34:00') as time,formatDateTime(time, '%Y') AS def_datetime_year;
┌────────────────time─┬─def_datetime_year─┐
│ 2021-08-21 17:34:00 │ 2021              │
└─────────────────────┴───────────────────┘

SELECT toDateTime('2021-08-21 17:34:00') as time,formatDateTime(time, '%y') AS def_datetime_year_litter;
┌────────────────time─┬─def_datetime_year_litter─┐
│ 2021-08-21 17:34:00 │ 21                       │
└─────────────────────┴──────────────────────────┘

SELECT toDateTime('2021-08-21 17:34:00') as time,formatDateTime(time, '%m') AS def_datetime_month;
┌────────────────time─┬─def_datetime_year─┐
│ 2021-08-21 17:34:00 │ 08                │
└─────────────────────┴───────────────────┘

SELECT toDateTime('2021-08-21 17:34:00') as time,formatDateTime(time, '%d') AS def_datetime_month;
┌────────────────time─┬─def_datetime_month─┐
│ 2021-08-21 17:34:00 │ 21                 │
└─────────────────────┴────────────────────┘

SELECT toDateTime('2021-08-21 17:34:00') as time,formatDateTime(time, '%H') AS hour24;
┌────────────────time─┬─hour24─┐
│ 2021-08-21 17:34:00 │ 17     │
└─────────────────────┴────────┘

SELECT toDateTime('2021-08-21 17:34:00') as time,formatDateTime(time, '%I') AS hour12;
┌────────────────time─┬─hour12─┐
│ 2021-08-21 17:34:00 │ 05     │
└─────────────────────┴────────┘

SELECT toDateTime('2021-08-21 17:34:00') as time,formatDateTime(time, '%M') AS def_datetime_get_minute;
┌────────────────time─┬─def_datetime_get_minute─┐
│ 2021-08-21 17:34:00 │ 34                      │
└─────────────────────┴─────────────────────────┘

SELECT toDateTime('2021-08-21 17:34:00') as time,formatDateTime(time, '%S') AS def_datetime_get_second;
┌────────────────time─┬─def_datetime_get_second─┐
│ 2021-08-21 17:34:00 │ 00                      │
└─────────────────────┴─────────────────────────┘

SELECT toDateTime('2021-08-21 17:34:00') as time,formatDateTime(time, '%w') AS def_datetime_get_curWeek;
┌────────────────time─┬─def_datetime_get_curWeek─┐
│ 2021-08-21 17:34:00 │ 6                        │
└─────────────────────┴──────────────────────────┘

SELECT toDateTime('2021-08-21 17:34:00') as time,formatDateTime(time, '%p') AS PMorAM;
┌────────────────time─┬─PMorAM─┐
│ 2021-08-21 17:34:00 │ PM     │
└─────────────────────┴────────┘

SELECT toDateTime('2021-08-21 17:34:00') as time,formatDateTime(time, '%D') AS now_time_day_month_year;
┌────────────────time─┬─now_time_day_month_year─┐
│ 2021-08-21 17:34:00 │ 08/21/21                │
└─────────────────────┴─────────────────────────┘

addYears/Months…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
WITH
toDate('2021-08-21') as date,
toDateTime('2021-08-21 17:34:00') AS time
SELECT  
addYears(date, 1) AS add_years_with_date,
addYears(time, 1) AS add_years_with_date_time;
┌─add_years_with_date─┬─add_years_with_date_time─┐
│          2022-08-21 │      2022-08-21 17:34:00 │
└─────────────────────┴──────────────────────────┘

WITH
toDate('2021-08-21') as date,
toDateTime('2021-08-21 17:34:00') AS time
SELECT 
addMonths(date, 1) AS add_months_with_date,
addMonths(time, 1) AS add_months_with_date_time;
┌─add_months_with_date─┬─add_months_with_date_time─┐
│           2021-09-21 │       2021-09-21 17:34:00 │
└──────────────────────┴───────────────────────────┘

WITH
toDate('2021-08-21') as date,
toDateTime('2021-08-21 17:34:00') AS time
SELECT 
addWeeks(date, 1) AS add_weeks_with_date,
addWeeks(time, 1) AS add_weeks_with_date_time;
┌─add_weeks_with_date─┬─add_weeks_with_date_time─┐
│          2021-08-28 │      2021-08-28 17:34:00 │
└─────────────────────┴──────────────────────────┘

WITH
toDate('2021-08-21') as date,
toDateTime('2021-08-21 17:34:00') AS time
SELECT 
addDays(date, 1) AS add_days_with_date,
addDays(time, 1) AS add_days_with_date_time;
┌─add_days_with_date─┬─add_days_with_date_time─┐
│         2021-08-22 │     2021-08-22 17:34:00 │
└────────────────────┴─────────────────────────┘

WITH
toDateTime('2021-08-21 17:34:00') AS time
SELECT 
addHours(time, 1) AS add_hours_with_date_time,
addMinutes(time, 1) AS add_minutes_with_date_time,
addSeconds(time, 10) AS add_seconds_with_date_time;
┌─add_hours_with_date_time─┬─add_minutes_with_date_time─┬─add_seconds_with_date_time─┐
│      2021-08-21 18:34:00 │        2021-08-21 17:35:00 │        2021-08-21 17:34:10 │
└──────────────────────────┴────────────────────────────┴────────────────────────────┘


WITH
toDateTime('2021-08-21 17:34:00') AS time
SELECT 
addQuarters(time, 1) AS add_quarters_with_date,
addQuarters(time, 1) AS add_quarters_with_date_time;
┌─add_quarters_with_date─┬─add_quarters_with_date_time─┐
│    2021-11-21 17:34:00 │         2021-11-21 17:34:00 │
└────────────────────────┴─────────────────────────────┘

subtractYears/Months…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- 2.跳转到当前日期之前的函数(函数将Date/DateTime减去一段时间间隔，然后返回Date/DateTime)
WITH
toDate('2021-08-21') as date,
toDateTime('2021-08-21 17:34:00') AS time
SELECT
subtractYears(date, 1) AS subtract_years_with_date,
subtractYears(time, 1) AS subtract_years_with_date_time;
┌─subtract_years_with_date─┬─subtract_years_with_date_time─┐
│               2020-08-21 │           2020-08-21 17:34:00 │
└──────────────────────────┴───────────────────────────────┘

WITH
toDate('2021-08-21') as date,
toDateTime('2021-08-21 17:34:00') AS time
SELECT
subtractQuarters(date, 1) AS subtract_Quarters_with_date,
subtractQuarters(time, 1) AS subtract_Quarters_with_date_time;
┌─subtract_Quarters_with_date─┬─subtract_Quarters_with_date_time─┐
│                  2021-05-21 │              2021-05-21 17:34:00 │
└─────────────────────────────┴──────────────────────────────────┘

WITH
toDate('2021-08-21') as date,
toDateTime('2021-08-21 17:34:00') AS time
SELECT
subtractMonths(date, 1) AS subtract_Months_with_date,
subtractMonths(time, 1) AS subtract_Months_with_date_time;
┌─subtract_Months_with_date─┬─subtract_Months_with_date_time─┐
│                2021-07-21 │            2021-07-21 17:34:00 │
└───────────────────────────┴────────────────────────────────┘

WITH
toDate('2021-08-21') as date,
toDateTime('2021-08-21 17:34:00') AS time
SELECT
subtractWeeks(date, 1) AS subtract_Weeks_with_date,
subtractWeeks(time, 1) AS subtract_Weeks_with_date_time;

WITH
toDate('2021-08-21') as date,
toDateTime('2021-08-21 17:34:00') AS time
SELECT
subtractDays(date, 1) AS subtract_Days_with_date,
subtractDays(time, 1) AS subtract_Days_with_date_time;

select 
toDateTime('2021-08-21 17:34:00') AS time,
subtractHours(time, 1) AS subtract_Hours_with_date_time,
subtractMinutes(time, 1) AS subtract_Minutes_with_date_time,
subtractSeconds(time, 1) AS subtract_Seconds_with_date_time;

dateDiff

  • 计算两个时间的差值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

select 
toDateTime('2021-08-21 17:34:00') AS time1,
toDateTime('2025-08-21 17:34:00') AS time2,
dateDiff('year', time1, time2) as diff_years;
┌───────────────time1─┬───────────────time2─┬─diff_years─┐
│ 2021-08-21 17:34:00 │ 2025-08-21 17:34:00 │          4 │
└─────────────────────┴─────────────────────┴────────────┘

select 
toDateTime('2021-08-21 17:34:00') AS time1,
toDateTime('2025-08-21 17:34:00') AS time2,
dateDiff('month', time1, time2) as diff_month;
┌───────────────time1─┬───────────────time2─┬─diff_month─┐
│ 2021-08-21 17:34:00 │ 2025-08-21 17:34:00 │         48 │
└─────────────────────┴─────────────────────┴────────────┘

select 
toDateTime('2021-08-21 17:34:00') AS time1,
toDateTime('2025-08-21 17:34:00') AS time2,
dateDiff('week', time1, time2) as diff_week;
┌───────────────time1─┬───────────────time2─┬─diff_week─┐
│ 2021-08-21 17:34:00 │ 2025-08-21 17:34:00 │       209 │
└─────────────────────┴─────────────────────┴───────────┘

select 
toDateTime('2021-08-21 17:34:00') AS time1,
toDateTime('2025-08-21 17:34:00') AS time2,
dateDiff('day', time1, time2) as diff_days;
┌───────────────time1─┬───────────────time2─┬─diff_days─┐
│ 2021-08-21 17:34:00 │ 2025-08-21 17:34:00 │      1461 │
└─────────────────────┴─────────────────────┴───────────┘

select 
toDateTime('2021-08-21 17:34:00') AS time1,
toDateTime('2025-08-21 17:34:00') AS time2,
dateDiff('hour', time1, time2) as diff_hours;
┌───────────────time1─┬───────────────time2─┬─diff_hours─┐
│ 2021-08-21 17:34:00 │ 2025-08-21 17:34:00 │      35064 │
└─────────────────────┴─────────────────────┴────────────

select 
toDateTime('2021-08-21 17:34:00') AS time1,
toDateTime('2025-08-21 17:34:00') AS time2,
dateDiff('minute', time1, time2) as diff_minutes;
┌───────────────time1─┬───────────────time2─┬─diff_minutes─┐
│ 2021-08-21 17:34:00 │ 2025-08-21 17:34:00 │      2103840 │
└─────────────────────┴─────────────────────┴──────────────┘

select 
toDateTime('2021-08-21 17:34:00') AS time1,
toDateTime('2025-08-21 17:34:00') AS time2,
dateDiff('second', time1, time2) as diff_seconds;
┌───────────────time1─┬───────────────time2─┬─diff_seconds─┐
│ 2021-08-21 17:34:00 │ 2025-08-21 17:34:00 │    126230400 │
└─────────────────────┴─────────────────────┴──────────────┘

timeSlot

  • timeSlot(StartTime, Duration, [,Size])
  • 返回一个时间数组
  • 其中包括从从 StartTime 开始到 StartTime + Duration 秒 内的所有符合size（以秒为单位）步长的时间点
  • 搜索在相应会话中综合浏览量是非常有用的
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 从 2021-08-21 01:00:00 开始，每十秒生成一个元素 到 2021-08-21 01:00:00 + 60 秒截止，将所有的元素组成一个数组
SELECT
timeSlots(toDateTime('2021-08-21 01:00:00'), toUInt32(60),10) as dateTimeArray;
-- 返回结果
[
  '2012-01-01 12:20:00',
  '2012-01-01 12:20:10',
  '2012-01-01 12:20:20',
  '2012-01-01 12:20:30',
  '2012-01-01 12:20:40',
  '2012-01-01 12:20:50',
  '2012-01-01 12:21:00'
]

-- 查询数据
with 
timeSlots(toDateTime('2021-08-21 01:00:00'), toUInt32(60),10) as timeArray
select 
timeArray[0] as arr_index_0,
timeArray[2] as arr_index_2,
timeArray[4] as arr_index_3,
timeArray[6] as arr_index_4;
┌─────────arr_index_0─┬─────────arr_index_2─┬─────────arr_index_3─┬─────────arr_index_4─┐
│ 1970-01-01 08:00:00 │ 2021-08-21 01:00:10 │ 2021-08-21 01:00:30 │ 2021-08-21 01:00:50 │
└─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┘
文章作者: hnbian
文章链接: https://www.hnbian.cn/posts/48adb284.html
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 hnbian !
olap clickhouse
评论
  目录