1.3.6.2.2. 转换为 CLICKHOUSE

1.3.6.2.2.1. select_first, select_last 聚合函数

描述

支持对select_first, select_last 聚合函数的转换, 转换为argMin, argMax

语法

支持的格式如下(同influxdb3)：

select_first(expr, time)['time']
select_first(expr, time)['value']
select_last(expr, time)['time']
select_last(expr, time)['value']

示例

-- 源SQL:
select selector_first(my_field, time)['time'] as _time,
selector_first(my_field, time)['value']
from base_test
where my_tag='val6'
and time <= '1970-01-10T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by _time

select convert_tz(selector_last(my_field, time)['time'], 'UTC', 'Asia/Shanghai') as _time,
    selector_last(my_field, time)['value']
from base_test
where my_tag='val6'
and time <= '1970-01-10T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by _time


-- 目标SQL:
SELECT argMin(tuple(my_field, time), time).2 AS _time,
argMin(tuple(my_field, time), time).1
FROM base_test
WHERE my_tag='val6'
AND time<=parseDateTime64BestEffort('1970-01-10T00:00:00Z', 9)
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 9)
ORDER BY _time


SELECT toTimeZone(argMax(tuple(my_field, time), time).2, 'Asia/Shanghai') AS _time,
argMax(tuple(my_field, time), time).1
FROM base_test
WHERE my_tag='val6'
AND time<=parseDateTime64BestEffort('1970-01-10T00:00:00Z', 9)
AND time>=parseDateTime64BestEffort('1970-01-01T00:00:00Z', 9)
ORDER BY _time

1.3.6.2.2.2. 时间格式to_char/date_format

描述

支持对to_char/date_format设置时间格式的转换，转换为formatDateTime

语法

支持的格式如下：

to_char/date_format(expr, format)

警告

• 21.4.5.46 版本的formatDateTime的format不支持f(毫秒),z(时区)

示例

-- 源SQL:
select date_format(time, '%Y-%m-%dT%H:%M:%S'), my_tag, my_field from base_test where my_tag='val6'
select date_format(convert_tz(time, 'UTC','Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S'), my_tag, my_field from base_test where my_tag='val6'


-- 目标SQL:
SELECT formatDateTime(time, '%Y-%m-%dT%H:%M:%S'),my_tag,my_field FROM base_test WHERE my_tag='val6'
SELECT formatDateTime(toTimeZone(time, 'Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S'),my_tag,my_field FROM base_test WHERE my_tag='val6'

1.3.6.2.2.3. now

描述

now 转换为 now64, 在clickhouse中now 只到秒

语法

支持的格式如下：

now()

示例

-- 源SQL:
select date_format(time, '%Y-%m-%dT%H:%M:%S'), my_tag, my_field from base_test where my_tag='val6'
select date_format(convert_tz(time, 'UTC','Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S'), my_tag, my_field from base_test where my_tag='val6'


-- 目标SQL:
SELECT formatDateTime(time, '%Y-%m-%dT%H:%M:%S'),my_tag,my_field FROM base_test WHERE my_tag='val6'
SELECT formatDateTime(toTimeZone(time, 'Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S'),my_tag,my_field FROM base_test WHERE my_tag='val6'