ClickHouse函数 3.时间函数


转换格式

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:002021-08-21 17:34:002021-08-212021-08-21 17:34:002021-08-21 17:34:00 │
└─────────────────────┴─────────────────────┴────────────┴─────────────────────┴─────────────────────┘

时间计算

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:002021-08-21 17:35:002021-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:002021-08-21 18:34:002021-08-21 17:35:002021-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:002021-08-21 17:35:002021-08-21 17:34:01 │
└───────────────────────────────┴────────────────────────────────────┴────────────────────────────────────┘

日期计算

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-282021-08-282021-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-212021-08-282021-08-282021-09-21 │
└────────────┴───────────────┴──────────────────┴───────────────────┘

toUnixTimestamp

  • toUnixTimestamp(DateTime)
  • 将DateTime转换成Unix时间戳

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

now

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

toDate

-- 保留 时-分-秒
select toDateTime('2021-08-21 17:34:00') AS time, toDate(time) as date_local;
┌────────────────time─┬─date_local─┐
│ 2021-08-21 17:34:002021-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:002021-08-21 │
└─────────────────────┴───────────────┘

today

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

toTime

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

toYear

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

toMonth

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

toQuarter

-- 一年分为四个季度。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:003 │
└─────────────────────┴─────────────┘

toHour

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

toMinute

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

toSecond

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

toDayOfYear

-- 获取 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:00233 │
└─────────────────────┴────────────────────────────────────────────────┘

toDayOfMonth

-- 当前月份的第几天
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:0021 │
└─────────────────────┴─────────────────────────────────────────────────┘

toDayOfWeek

-- 星期
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:006 │
└─────────────────────┴────────────────────────────────────────────────┘

toDateTime

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:002021-08-21 17:34:00 │
└─────────────────────┴─────────────────────┘

toStartOfYear

-- 得到当前年份的第一天,当前月份的第一天,当前季度的第一天,当前日期的开始时刻
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:002021-01-01 │
└─────────────────────┴──────────────────────────────────────────────────┘

toStartOfMonth

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:002021-08-01 │
└─────────────────────┴───────────────────────────────────────────────────┘

toStartOfQuarter

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:002021-07-01 │
└─────────────────────┴─────────────────────────────────────────────────────┘

toStartOfDay

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:002021-08-21 00:00:00 │
└─────────────────────┴─────────────────────────────────────────────────┘

toStartOfHour

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:002021-08-21 17:00:00 │
└─────────────────────┴──────────────────────────────────────────────────┘

toStartOfMinute

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:002021-08-21 17:34:00 │
└─────────────────────┴────────────────────────────────────────────────────┘

toRelativeYearNum

-- 从过去的某个固定的时间开始,以此得到当前指定的日期的编号
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:002021 │
└─────────────────────┴──────────────────────────────────────────────────────┘

toRelativeQuarterNum

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:008086 │
└─────────────────────┴─────────────────────────────────────────────────────────┘

toISOYear

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

toISOWeek

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

yesterday

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

toYYYYMMDDhhmmss

-- 目前只有这三种格式,没有什么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:0020210821173400 │
└─────────────────────┴─────────────────────────────────────────────────────┘

toYYYYMMDD

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:0020210821 │
└─────────────────────┴───────────────────────────────────────────────┘

toYYYYMM

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:00202108 │
└─────────────────────┴─────────────────────────────────────────────┘

formatDateTime

  • formatDateTime(Time, Format[,Timezone])
  • 函数引用
表达式 含义
%F 抽取日期部分
%T 抽取时间部分
%Y 抽取年份
%y 抽取年份后两位,(00-99)
%m 抽取月
%d 抽取 日
%H 抽取 时
%I 抽取 时,转为 12 小时制
%M 抽取 分 部分
%S 抽取 秒 部分
%w 判断日期为周几
%D 抽取日期,转为 日/月/年 的格式
%p 判断时间是上午还是下午
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:002021-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:0017: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:002021              │
└─────────────────────┴───────────────────┘

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:0021                       │
└─────────────────────┴──────────────────────────┘

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:0008                │
└─────────────────────┴───────────────────┘

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:0021                 │
└─────────────────────┴────────────────────┘

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

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

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:0034                      │
└─────────────────────┴─────────────────────────┘

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:0000                      │
└─────────────────────┴─────────────────────────┘

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:006                        │
└─────────────────────┴──────────────────────────┘

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:0008/21/21                │
└─────────────────────┴─────────────────────────┘

addYears/Months…

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-212022-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-212021-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-282021-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-222021-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:002021-08-21 17:35:002021-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:002021-11-21 17:34:00 │
└────────────────────────┴─────────────────────────────┘

subtractYears/Months…

-- 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-212020-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-212021-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-212021-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

  • 计算两个时间的差值

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:002025-08-21 17:34:004 │
└─────────────────────┴─────────────────────┴────────────┘

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:002025-08-21 17:34:0048 │
└─────────────────────┴─────────────────────┴────────────┘

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:002025-08-21 17:34:00209 │
└─────────────────────┴─────────────────────┴───────────┘

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:002025-08-21 17:34:001461 │
└─────────────────────┴─────────────────────┴───────────┘

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:002025-08-21 17:34:0035064 │
└─────────────────────┴─────────────────────┴────────────

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:002025-08-21 17:34:002103840 │
└─────────────────────┴─────────────────────┴──────────────┘

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:002025-08-21 17:34:00126230400 │
└─────────────────────┴─────────────────────┴──────────────┘

timeSlot

  • timeSlot(StartTime, Duration, [,Size])
  • 返回一个时间数组
  • 其中包括从从 StartTime 开始到 StartTime + Duration 秒 内的所有符合size(以秒为单位)步长的时间点
  • 搜索在相应会话中综合浏览量是非常有用的
-- 从 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:002021-08-21 01:00:102021-08-21 01:00:302021-08-21 01:00:50 │
└─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┘

文章作者: hnbian
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 hnbian !
评论
 上一篇
ClickHouse函数 4.数组函数 ClickHouse函数 4.数组函数
1. empty 判断数组是否为空 为空:1 不为空:0 SELECT empty([]), empty([1,2,3]); ┌─empty(array())─┬─empty([1, 2, 3])─┐ │ 1
2021-09-10
下一篇 
ClickHouse函数汇总 ClickHouse函数汇总
1. 算术函数官网介绍算数函数地址 — – 求和 plus 求差值 minus 求积 multiply 求商 divide 求余数 modulo 取反 negate 绝对值 abs 最大公约数 gcd 最小公倍数 l
2021-08-20
  目录