ClickHouse函数 3.时间函数

clickhouse

发布日期: 2021-08-25

文章字数: 2.8k

阅读时长: 16 分

阅读次数:

转换格式

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

时间计算

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

日期计算

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时间戳


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

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: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

-- 其功能与'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

-- 将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

-- 获取年份
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

-- 获取月份
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（一季度: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

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

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

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

-- 获取 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

-- 当前月份的第几天
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

-- 星期
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

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

-- 得到当前年份的第一天,当前月份的第一天，当前季度的第一天，当前日期的开始时刻
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

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

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

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

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

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

-- 从过去的某个固定的时间开始，以此得到当前指定的日期的编号
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

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

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

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

-- 当前日期的上一天
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:00 │                                      20210821173400 │
└─────────────────────┴─────────────────────────────────────────────────────┘

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

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

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: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…

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…

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

计算两个时间的差值


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（以秒为单位）步长的时间点
搜索在相应会话中综合浏览量是非常有用的

-- 从 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 !

clickhouse olap

ClickHouse函数 4.数组函数

1. empty 判断数组是否为空为空：1 不为空：0 SELECT empty([]), empty([1,2,3]); ┌─empty(array())─┬─empty([1, 2, 3])─┐ │ 1

2021-09-10 clickhouse

clickhouse olap

ClickHouse函数汇总

1. 算术函数官网介绍算数函数地址 — – 求和 plus 求差值 minus 求积 multiply 求商 divide 求余数 modulo 取反 negate 绝对值 abs 最大公约数 gcd 最小公倍数 l

2021-08-20 clickhouse

clickhouse olap

ClickHouse函数 3.时间函数

转换格式

时间计算

日期计算

toUnixTimestamp

now

toDate

today

toTime

toYear

toMonth

toQuarter

toHour

toMinute

toSecond

toDayOfYear

toDayOfMonth

toDayOfWeek

toDateTime

toStartOfYear

toStartOfMonth

toStartOfQuarter

toStartOfDay

toStartOfHour

toStartOfMinute

toRelativeYearNum

toRelativeQuarterNum

toISOYear

toISOWeek

yesterday

toYYYYMMDDhhmmss

toYYYYMMDD

toYYYYMM

formatDateTime

addYears/Months…

subtractYears/Months…

dateDiff

timeSlot

你的赏识是我前进的动力