1.3.6.3.1. INFLUXDB1

1.3.6.3.1.1. 时间间隔分区

描述

在INFLUXDB1中时间间隔分区通过 group by time(intervalExpr) fill(fillMode) 进行
在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'。

-- 5. 格式5，对date_bin_gapfill 进行操作（比如 date_format保证日期格式一致，java 接口不需要）,需要group by date_bin_gapfill(intervalExpr, time)
SELECT operator(date_bin_gapfill(intervalExpr, time)) as alias_name,
expr_with_fill_func...
FROM table [WHERE condition]
GROUP BY date_bin_gapfill(intervalExpr, time), expr...
[ ORDER BY ... ]

fill_func: locf|interpolate|coalesce

警告

influxql group by 只能为tag 或time(), 因此TSDB SQL 转换后也需要这样，不然转换后执行出错
influxql order by 只能time, 因此TSDB SQL 也限制这样，不然转换后执行出错
influxql 不能只查询tag, 因此TSDB SQL 也限制这样，不然转换后执行出错
influxql where 中不支持or, 因此TSDB SQL 也限制这样，不然转换后执行出错
influxql 对于float 类型会去除末尾的0，和TSDB SQL 结构不一致（不会去除）
格式1,2 不能直接对查询列date_bin_gapfill返回的时间进行操作(date_bin 可以)，需要通过子查询实现
格式1,2 ，influxdb3和influxdb1 时间字段的值格式不同, 若需要一致需要使用格式3，套上date_format 函数转换格式为 '%Y-%m-%dT%H:%M:%S%.fZ' 或 '%Y-%m-%dT%H:%M:%S%.f%:z'
- 使用date_format 转换后的格式 %.f 表示9位精度小数，但influxdb1 中使用最短精度，会去掉末尾的0。仍可能不一致
格式5 可以对date_bin_gapfill/date_bin返回的时间进行操作，可以进行转换时区，转换时间格式等操作
fillmode 对应关系:
- date_bin 对应 fill(none)
- date_bin_gapfill 对应 fill(null)
- date_bin_gapfill+lcof 对应 fill(previous)
- date_bin_gapfill+interpolate 对应 fill(linear)
- date_bin_gapfill+coalesce 对应 fill(num)
- influxdb1 默认为fill(null)
不能混合使用lcof，interpolate，coalesce；使用了填充函数，则所有需要填充的查询列都要使用。

使用 interpolate时, 对于 '1970-01-01T00:00:00Z' 后若有空行且使用了时区转换，填充使用的是 '1970-01-01T00:00:00Z' 的值

SELECT time AS _time,mean(val) FROM test1 WHERE key1='k1' AND time<='1970-01-12T00:00:00Z' AND time>='1970-01-01T00:00:00Z' GROUP BY time(1d) fill(linear) ORDER BY time ;
name: test1
_time                 mean
----                 ----
1970-01-01T00:00:00Z 2
1970-01-02T00:00:00Z 2.5
1970-01-03T00:00:00Z 3
1970-01-04T00:00:00Z 3.5
1970-01-05T00:00:00Z 4
1970-01-06T00:00:00Z 4.5
1970-01-07T00:00:00Z 5
1970-01-08T00:00:00Z 5.333333333333333
1970-01-09T00:00:00Z 5.666666666666667
1970-01-10T00:00:00Z 6
1970-01-11T00:00:00Z 6.5
1970-01-12T00:00:00Z 7
SELECT time AS _time,mean(val) FROM test1 WHERE key1='k1' AND time<='1970-01-12T00:00:00Z' AND time>='1970-01-01T00:00:00Z' GROUP BY time(1d) fill(linear) ORDER BY time TZ('Asia/Shanghai');
name: test1
_time                      mean
----                      ----
1970-01-01T00:00:00+08:00 2
1970-01-02T00:00:00+08:00 2
1970-01-03T00:00:00+08:00 3
1970-01-04T00:00:00+08:00 3.5
1970-01-05T00:00:00+08:00 4
1970-01-06T00:00:00+08:00 4.5
1970-01-07T00:00:00+08:00 5
1970-01-08T00:00:00+08:00 5.333333333333333
1970-01-09T00:00:00+08:00 5.666666666666667
1970-01-10T00:00:00+08:00 6
1970-01-11T00:00:00+08:00 6.5
1970-01-12T00:00:00+08:00 7

示例

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

select date_bin_gapfill(INTERVAL 1 day, time) as _time,
    locf(avg(my_field)),
    locf(count(my_field))+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,
    locf(avg(my_field)),
    locf(count(my_field))+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


--- 格式5：
select date_format(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:
--- 格式1：
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(none)
ORDER BY time;

--- 格式2：
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(none)
ORDER BY time;

--- 格式3：
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 ;

SELECT time AS _time,mean(my_field),count(my_field)+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(1d) fill(previous)
ORDER BY time;

--- 格式4：
SELECT time AS _time,mean(my_field),count(my_field)+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(1d) fill(previous)
ORDER BY time TZ('Asia/Shanghai');


--- 格式5：
SELECT time AS _time,mean(my_field),count(my_field)+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(1d) fill(previous)
ORDER BY time TZ('Asia/Shanghai');

1.3.6.3.1.2. 时区转换

描述

在INFLUXDB1中时区转换通过 tz(‘timezone’) 字句实现, 只影响time字段
- time 转时区是把time 当成utc 时区进行偏移转换
- 按时间间隔分组使用tz 转时区，会对间隔边界转换时区，再分区
在TSDB SQL中按时间间隔分组转换通过对表达式套上 convert_tz(expr, 'UTC', 'timezone')

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

tz(expr, 'timezone') -- (同influxdb3 SQL)
convert_tz(expr, 'UTC', 'timezone')
convert_tz(expr, '+00:00', 'timezone')

警告

使用convert_tz/tz， convert_tz 为mysql 函数
convert_tz 转换为 INFLUXDB3 中的tz, influxdb1 中tz 子句

示例

-- 源SQL:
select date_format(tz(time, 'Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S%.f%:z'),
    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'
order by time

select date_format(convert_tz(time, 'utc', 'Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S%.f%:z'),
    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'
order by time



-- 目标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'
ORDER BY time TZ('Asia/Shanghai');

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'
ORDER BY time TZ('Asia/Shanghai');

1.3.6.3.1.3. interval 表达式

描述

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

语法

支持的TSDB SQL格式如下：

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

time_unit:
    nanosecond
    |microsecond
    |millisecond
    |second
    |minute
    |hour
    |day
    |week
    |nanoseconds
    |microseconds
    |milliseconds
    |seconds
    |minutes
    |hours
    |days
    |weeks
    | SECOND_MICROSECOND
    | MINUTE_MICROSECOND
    | MINUTE_SECOND
    | HOUR_MICROSECOND
    | HOUR_SECOND
    | HOUR_MINUTE
    | DAY_MICROSECOND
    | DAY_SECOND
    | DAY_MINUTE
    | DAY_HOUR

警告

mysql 不支持nanosecond，millisecond，为扩充
不支持 year, month, quarter, year_month - influxdb1 没有对应的

示例

-- 源SQL:
select date_format(tz(time, 'Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S%.f%:z'),
    my_tag,
    my_field
from base_test
where my_tag='val5'
and time <=  now() - interval '1 weeks 1 days 9 hours 1 minutes 1 seconds 1 milliseconds 2 microseconds 3 nanoseconds'
and time >= '1970-01-01T00:00:00Z'
order by time

select date_format(tz(time, 'Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S%.f%:z'),
    my_tag,
    my_field
from base_test
where my_tag='val5'
and time <=  now() - interval 1 days
and time >= '1970-01-01T00:00:00Z'
order by time

select date_format(tz(time, 'Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S%.f%:z'),
    my_tag,
    my_field
from base_test
where my_tag='val5'
and time <= now() - interval '1 3:20:30' day_second
and time >= '1970-01-01T00:00:00Z'
order by time

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

SELECT time,my_tag,my_field
FROM base_test
WHERE my_tag='val5' AND time<=now()-1d AND time>='1970-01-01T00:00:00Z'
ORDER BY time TZ('Asia/Shanghai');

SELECT time,my_tag,my_field
FROM base_test
WHERE my_tag='val5' AND time<=now()-1d3h20m30s AND time>='1970-01-01T00:00:00Z'
ORDER BY time TZ('Asia/Shanghai');

1.3.6.3.1.4. 正则比较过滤

描述

支持对regexp_like正则比较的转换

语法

支持的正则比较 SQL格式如下(influxdb3，mysql 相同)：

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

警告

pattern 只能是字符串常量

示例

-- 源SQL:
select date_format(tz(time, 'Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S%.f%:z') 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 date_format(tz(time, 'Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S%.f%:z') 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 date_format(tz(time, 'Asia/Shanghai'), '%Y-%m-%dT%H:%M:%S%.f%:z') 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 time AS _time,room,hum
FROM test_pattern
WHERE room =~ /\\Kitchen/
AND time<='1970-01-25T00:00:00Z' AND time>='1970-01-01T00:00:00Z'
ORDER BY time TZ('Asia/Shanghai');


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

-- 带斜杠
SELECT time AS _time,room,hum
FROM test_pattern
WHERE room =~ /\/Kitchen/
AND time<='1970-01-25T00:00:00Z' AND time>='1970-01-01T00:00:00Z'
ORDER BY time TZ('Asia/Shanghai');