1.3.6.3.3. CLICKHOUSE

1.3.6.3.3.1. 时间比较操作转换

描述

会对时间和字符串或时间的比较操作进行转换

语法

支持的格式如下：

time compare_op time_str
time_str compare_op time
time compare_op timestamp_num
timestamp_num compare_op time

备注

• 转换后精度为毫秒， clickhouse 默认精度为毫秒

• time_str 带时区使用parseDateTime64BestEffort转换

• time_str 不带时区和时间戳使用toDateTime64转换

示例

-- 源SQL:
select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
room,
hum
from test_pattern
WHERE regexp_like(room, '\\Kitchen') = true
and time <= '1970-01-25T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by _time

select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
room,
hum
from test_pattern
WHERE regexp_like(room, '\\Kitchen') = true
and time <= 10000
and time >= 0
order by _time


-- 目标SQL:
SELECT toTimeZone(time, 'Asia/Shanghai') AS _time,room,hum
FROM test_pattern
WHERE match(room, '\\\\Kitchen')=1
AND time<=parseDateTime64BestEffort('1970-01-25T00:00:00Z', 3)
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)
ORDER BY _time


SELECT toTimeZone(time, 'Asia/Shanghai') AS _time,room,hum
FROM test_pattern
WHERE match(room, '\\\\Kitchen')=1
AND time<=toDateTime64(10000, 3, 'UTC')
AND time>=toDateTime64(0, 3, 'UTC')
ORDER BY _time

-- 带斜杠
SELECT toTimeZone(time, 'Asia/Shanghai') AS _time,room,hum
FROM test_pattern
WHERE match(room, '/Kitchen')=1
AND time<=parseDateTime64BestEffort('1970-01-25T00:00:00Z', 3)
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)
ORDER BY _time

1.3.6.3.3.2. 时间字符串加减时间间隔

描述

支持时间字符串直接加减时间间隔

语法

支持的时区转换格式如下：

expr + IntervalTimeExpr
IntervalTimeExpr + expr
expr - IntervalTimeExpr

备注

• 会对时间字符串或时间戳数字进行转换，转换规则同 时间比较操作转换

示例

-- 源SQL:
select time,
    my_tag,
    my_field
from base_test
where my_tag='val5'
and time <= '1970-01-03T00:00:00Z'
and time >= '1970-01-01T00:00:00Z' + interval '1 days'
order by time


-- 目标SQL:
SELECT time,my_tag,my_field
FROM base_test WHERE my_tag='val5'
AND time<=parseDateTime64BestEffort('1970-01-03T00:00:00Z', 3)
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)+interval 86400 SECOND
ORDER BY time

1.3.6.3.3.3. 按时间间隔分组

描述

• 在CLICKHOUSE中按时间间隔分组通过 toStartOfInterval+with fill 和group by组合实现进行

• 在TSDB SQL中按时间间隔分组通过 date_bin 或者 date_bin_gapfill+locf/interpolate/coalesce 和group by组合实现（influxdb3 SQL）

语法

支持的按时间间隔分组 SQL格式如下(同influxdb3 SQL)：

-- 1. 格式1, date_bin_gapfill/date_bin为第一个查询列，group by 查询列别名（不能为time）
SELECT date_bin_gapfill/date_bin(intervalExpr, time) as alias_name,
expr...
FROM table [WHERE condition]
GROUP BY alias_name, expr...
[ ORDER BY ... ]

-- 2. 格式2，date_bin_gapfill/date_bin为第一个查询列，group by 查询列id
SELECT date_bin_gapfill/date_bin(intervalExpr, time) as alias_name,
expr...
FROM table [WHERE condition]
GROUP BY 1, expr...
[ ORDER BY ... ]

-- 3. 格式3，date_bin_gapfill为第一个查询列, 使用填充函数
SELECT date_bin_gapfill(intervalExpr, time) as alias_name,
expr_with_fill_func...
FROM table [WHERE condition]
GROUP BY alias_name, expr...
[ ORDER BY ... ]

fill_func: locf|interpolate|coalesce

-- 4. 格式4，时区转换
SELECT date_bin_gapfill(INTERVAL 1 day, convert_tz(time, 'UTC', timezone), origin_timestamp) as alias_name,
expr_with_fill_func...
FROM table [WHERE condition]
GROUP BY alias_name, expr...
[ ORDER BY ... ]

-- timezone: 比如：'Asia/Shanghai'
-- origin_timestamp: 默认为Unix epoch即'1970-01-01T00:00:00+00:00'，表示时间间隔初始边界。
-- 'Asia/Shanghai' 对应'1970-01-01T00:00:00+08:00'。

警告

• 格式1,2 ，clickhouse和influxdb1 时间字段的值格式不同, date_format函数不支持f(毫秒),z（时区）

• 不支持对date_bin_gapfill/date_bin返回的时间进行操作

• date_bin_gapfill/date_bin 没有时区转换时，默认为东8区

• fillmode 对应关系（influxdb1）:

  • toStartOfInterval 对应 fill(none)

  • fill(null) 不支持

  • fill(previous) 不支持

  • 对应 fill(linear) 不支持

  • 对应 fill(num) 不支持

  • toStartOfInterval + WITH FILL 可以实现填充字段默认值，只支持coalesce 函数

• 不能混合使用lcof，interpolate，coalesce；使用了填充函数，则所有需要填充的查询列都要使用。目前只能为coalesce

• 由于toStartOfInterval只能返回dateTime(精度不能小于秒，21.4.5.46), 不支持dateTime64按按时间间隔分组后时间最小只能到秒

• 如时间范围的最小边界小于UTC epoch, 执行会报错，比如如下SQL:

  select date_bin_gapfill(INTERVAL 1 day, convert_tz(time, 'UTC', 'Asia/Shanghai'), '1970-01-01T00:00:00+08:00') as _time,
      coalesce(avg(my_field),0),
      coalesce(count(my_field),0)+0.1
  from base_test
  where my_tag='val6'
  and time <= '1970-01-10T00:00:00Z'
  and time >= '1970-01-01T00:00:00Z'
  group by _time
  order by _time
  
  -- 转换后第一个间隔边界toStartOfInterval(parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3), toIntervalDay(1), 'Asia/Shanghai')
  -- 为'1970-01-10T00:00:00+08:00' 在clickhouse中为'2106-02-07 06:28:16'
  -- 会报错：不支持WITH FILL TO value cannot be less than FROM value for sorting in ascending direction
  SELECT toStartOfInterval(toTimeZone(time, 'Asia/Shanghai'), interval 1 DAY, 'Asia/Shanghai') AS _time,
  avg(my_field),count(my_field)+0.1
  FROM base_test WHERE my_tag='val6'
  AND time<=parseDateTime64BestEffort('1970-01-10T00:00:00Z', 3)
  AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)
  GROUP BY _time ORDER BY _time
  WITH FILL FROM toStartOfInterval(parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3), interval 1 DAY, 'Asia/Shanghai')
  TO toStartOfInterval(parseDateTime64BestEffort('1970-01-10T00:00:00Z', 3), interval 1 DAY, 'Asia/Shanghai')+interval 1 SECOND
  STEP datediff('second', now(), now()+interval 1 DAY)
  

示例

-- 源SQL:
--- 格式1：
select DATE_BIN(INTERVAL 1 day, time) as _time,
    avg(my_field) from base_test
where my_tag='val6'
and time <= '1970-01-10T00:00:00Z'and time >= '1970-01-01T00:00:00Z'
group by _time
order by _time

--- 格式2：
select DATE_BIN(INTERVAL 1 day, time) as _time,
    avg(my_field)
from base_test
where my_tag='val6'
and time <= '1970-01-10T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
group by 1
order by 1

--- 格式3：
select date_bin_gapfill(INTERVAL 1 day, time) as _time,
    coalesce(avg(my_field),0),
    coalesce(count(my_field),0)+0.1
from base_test
where my_tag='val6'
and time <= '1970-01-10T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
group by _time
order by _time

--- 格式4：
select date_bin_gapfill(INTERVAL 1 day, convert_tz(time, 'UTC', 'Asia/Shanghai'), '1970-01-01T00:00:00+08:00') as _time,
    coalesce(avg(my_field),0),
    coalesce(count(my_field),0)+0.1
from base_test
where my_tag='val6'
and time <= '1971-01-10T00:00:00Z'
and time >= '1971-01-01T00:00:00Z'
group by _time
order by _time


-- 目标SQL:
--- 格式1：
SELECT toStartOfInterval(time, interval 1 DAY, 'UTC') AS _time,avg(my_field)
FROM base_test WHERE my_tag='val6'
AND time<=parseDateTime64BestEffort('1970-01-10T00:00:00Z', 3)
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)
GROUP BY _time ORDER BY _time

--- 格式2：
SELECT toStartOfInterval(time, interval 1 DAY, 'UTC') AS _time,avg(my_field)
FROM base_test WHERE my_tag='val6'
AND time<=parseDateTime64BestEffort('1970-01-10T00:00:00Z', 3)
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)
GROUP BY _time ORDER BY 1

--- 格式3：
SELECT toStartOfInterval(time, interval 1 DAY, 'UTC') AS _time,
avg(my_field),count(my_field)+0.1
FROM base_test WHERE my_tag='val6'
AND time<=parseDateTime64BestEffort('1970-01-10T00:00:00Z', 3)
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)
GROUP BY _time
ORDER BY _time
WITH FILL FROM toStartOfInterval(parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3), interval 1 DAY, 'UTC')
TO toStartOfInterval(parseDateTime64BestEffort('1970-01-10T00:00:00Z', 3), interval 1 DAY, 'UTC')+interval 1 SECOND
STEP datediff('second', now(), now()+interval 1 DAY)

--- 格式4：
SELECT toStartOfInterval(toTimeZone(time, 'Asia/Shanghai'), interval 1 DAY, 'Asia/Shanghai') AS _time,
avg(my_field),count(my_field)+0.1 FROM base_test
WHERE my_tag='val6'
AND time<=parseDateTime64BestEffort('1971-01-10T00:00:00Z', 3)
AND time>=parseDateTime64BestEffort('1971-01-01T00:00:00Z', 3)
GROUP BY _time ORDER BY _time
WITH FILL
FROM toStartOfInterval(parseDateTime64BestEffort('1971-01-01T00:00:00Z', 3), interval 1 DAY, 'Asia/Shanghai')
TO toStartOfInterval(parseDateTime64BestEffort('1971-01-10T00:00:00Z', 3), interval 1 DAY, 'Asia/Shanghai')+interval 1 SECOND
STEP datediff('second', now(), now()+interval 1 DAY)

1.3.6.3.3.4. 时区转换

描述

• 在clickhouse中时区转换通过 toTimeZone(expr, timezone) 字句实现

• 在TSDB SQL中使用 convert_tz(expr, 'UTC', 'timezone')

语法 支持的时区转换格式如下：

-- 默认为UTC时间，因此源为UTC
convert_tz(expr, 'UTC', 'timezone')
convert_tz(expr, '+00:00', 'timezone')

示例

-- 源SQL:

select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
    my_tag,
    my_field
from base_test
where my_tag='val6'
and time <= '1970-01-03T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by _time



-- 目标SQL:
SELECT toTimeZone(time, 'Asia/Shanghai') AS _time,
my_tag,my_field
FROM base_test
WHERE my_tag='val6'
AND time<=parseDateTime64BestEffort('1970-01-03T00:00:00Z', 3)
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)
ORDER BY _time

1.3.6.3.3.5. interval 表达式

描述

支持对符合要求的interval 表达式进行转换

语法

支持的TSDB SQL格式如下：

interval 'num time_unit [num time_unit]...'
interval num time_unit -- (同influxdb3 SQL, 有扩展)

time_unit:
    second
    |minute
    |hour
    |day
    |week
    |seconds
    |minutes
    |hours
    |days
    |weeks
    | MINUTE_SECOND
    | HOUR_SECOND
    | HOUR_MINUTE
    | DAY_SECOND
    | DAY_MINUTE
    | DAY_HOUR

警告

• clickhouse21.4.5.46 不支持秒以下，因此不支持millisecond，microsecond，nanosecond，

• 也不支持SECOND_MICROSECOND，MINUTE_MICROSECOND，HOUR_MICROSECOND，DAY_MICROSECOND

• 不支持在按时间间隔分组中使用混合的时间单位的间隔

示例

-- 源SQL:
select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
    my_tag,
    my_field
from base_test
where my_tag='val6'
and time <=  now() - interval '1 weeks 1 days 9 hours 1 minutes 1 seconds'
and time >= '1970-01-01T00:00:00Z'
order by _time

select convert_tz(time, 'utc', 'Asia/Shanghai'),
    my_tag,
    my_field
from base_test
where my_tag='val6'
and time <=  now() - interval 1 days
and time >= '1970-01-01T00:00:00Z'
order by time

select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
    my_tag,
    my_field
from base_test
where my_tag='val6'
and time <= now() - interval '1 3:20:30' day_second
and time >= '1970-01-01T00:00:00Z'
order by _time

-- 目标SQL:
SELECT toTimeZone(time, 'Asia/Shanghai') AS _time,my_tag,my_field
FROM base_test
WHERE my_tag='val6'
AND time<=toDateTime64(now(), 3, 'UTC')-interval 723661 SECOND
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)
ORDER BY _time

SELECT toTimeZone(time, 'Asia/Shanghai'),my_tag,my_field
FROM base_test
WHERE my_tag='val6'
AND time<=toDateTime64(now(), 3, 'UTC')-interval 1 DAY
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)
ORDER BY time

SELECT toTimeZone(time, 'Asia/Shanghai') AS _time,my_tag,my_field
FROM base_test
WHERE my_tag='val6'
AND time<=toDateTime64(now(), 3, 'UTC')-interval 98430 SECOND
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)
ORDER BY _time

1.3.6.3.3.6. 正则比较过滤

描述

支持对regexp_like正则比较的转换

语法

支持的正则比较 SQL格式如下：

regexp_like(expr, pattern) = true/false
regexp_like(expr, pattern)

示例

-- 源SQL:
select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
room,
hum
from test_pattern
WHERE regexp_like(room, '\\Kitchen') = true
and time <= '1970-01-25T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by _time

select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
room,
hum
from test_pattern
WHERE regexp_like(room, '\(Room\)') = true
and time <= '1970-01-25T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by _time

-- 带斜杠
select convert_tz(time, 'utc', 'Asia/Shanghai') as _time,
room,
hum
from test_pattern
WHERE regexp_like(room, '/Kitchen') = true
and time <= '1970-01-25T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by _time

-- 目标SQL:
SELECT toTimeZone(time, 'Asia/Shanghai') AS _time,room,hum
FROM test_pattern
WHERE match(room, '\\\\Kitchen')=1
AND time<=parseDateTime64BestEffort('1970-01-25T00:00:00Z', 3)
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)
ORDER BY _time


SELECT toTimeZone(time, 'Asia/Shanghai') AS _time,room,hum
FROM test_pattern
WHERE match(room, '\\(Room\\)')=1
AND time<=parseDateTime64BestEffort('1970-01-25T00:00:00Z', 3)
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)
ORDER BY _time

-- 带斜杠
SELECT toTimeZone(time, 'Asia/Shanghai') AS _time,room,hum
FROM test_pattern
WHERE match(room, '/Kitchen')=1
AND time<=parseDateTime64BestEffort('1970-01-25T00:00:00Z', 3)
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 3)
ORDER BY _time