1.3.6.1.1. 转换为 INFLUXDB1

1.3.6.1.1.1. select_first, select_last 聚合函数

描述

支持对select_first, select_last 聚合函数的转换, 转换为first, last

语法

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

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

示例

-- 源SQL:
select to_char(tz(selector_first(my_field, time)['time'], 'Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S%.f%:z') as time,
selector_first(my_field, time)['value']
from base_test
where my_tag='val7'
and time <=  '1970-01-10T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by time

select to_char(tz(selector_last(my_field, time)['time'], 'Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S%.f%:z') as time,
    selector_last(my_field, time)['value']
from base_test
where my_tag='val7'
and time <=  '1970-01-10T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by time


-- 目标SQL:
SELECT time AS time,last(my_field)
FROM base_test
WHERE my_tag='val7' AND time<='1970-01-10T00:00:00Z' AND time>='1970-01-01T00:00:00Z'
ORDER BY time TZ('Asia/Shanghai');


SELECT time AS time,first(my_field)
FROM base_test
WHERE my_tag='val7' AND time<='1970-01-10T00:00:00Z' AND time>='1970-01-01T00:00:00Z'
ORDER BY time TZ('Asia/Shanghai');

1.3.6.1.1.2. avg聚合函数

描述

支持对avg聚合函数的转换，转换为mean

语法

支持的格式如下：

avg(expr)

示例

-- 源SQL:
select to_char(date_bin_gapfill(INTERVAL 1 day, time) at time zone 'Asia/Shanghai', '%Y-%m-%dT%H:%M:%S%.f%:z') 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 date_bin_gapfill(INTERVAL 1 day, time)
order by time

-- 目标SQL:
SELECT time AS time,mean(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(1d) fill(null)
ORDER BY time TZ('Asia/Shanghai');

1.3.6.1.1.3. 时间格式to_char

描述

支持对to_char设置时间格式的转换

语法

支持的格式如下：

to_char(expr, '%Y-%m-%dT%H:%M:%S%.fZ')  -- 转换为RFC3339 UTC时间 influxdb1 默认格式
to_char(expr, '%Y-%m-%dT%H:%M:%S%.f%:z') -- 转换为RFC3339 influxdb1 指定时区格式

警告

• %.f 表示9位精度小数，但influxdb1 中使用最短精度，会去掉末尾的0。可能不一致

示例

-- 源SQL:
select to_char(time, '%Y-%m-%dT%H:%M:%S%.fZ'), my_tag, my_field from base_test where my_tag='val1'
select to_char(tz(time, 'Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S%.f%:z'), my_tag, my_field from base_test where my_tag='val1'


-- 目标SQL:
SELECT time,my_tag,my_field FROM base_test WHERE my_tag='val1';
SELECT time,my_tag,my_field FROM base_test WHERE my_tag='val1' TZ('Asia/Shanghai');