1.3.6.2.1. INFLUXDB1

1.3.6.2.1.1. 时间分区

描述

在INFLUXDB1中时间分区通过 group by time(intervalExpr) fill(fillMode) 进行
在INFLUXDB3 SQL中时间分区通过date_bin或者 date_bin_gapfill+locf/interpolate/coalesce 和group by组合实现

语法

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

-- 1. 格式1, date_bin_gapfill/date_bin为第一个查询列，group by 查询列名
SELECT date_bin_gapfill/date_bin(intervalExpr, time) as _time,
expr...
FROM table [WHERE condition]
GROUP BY _time, 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/date_bin为第一个查询列, group by date_bin_gapfill/date_bin
SELECT operator(date_bin_gapfill/date_bin(intervalExpr, time)) [as alias_name],
expr...
FROM table [WHERE condition]
GROUP BY date_bin_gapfill/date_bin(intervalExpr, time), expr...
[ ORDER BY ... ]

-- 4. 格式3扩展（推荐用法），date_bin_gapfill/date_bin为第一个查询列, group by date_bin_gapfill/date_bin, 使用填充函数
SELECT operator(date_bin_gapfill/date_bin(intervalExpr, time)) [as alias_name],
expr_with_fill_func...
FROM table [WHERE condition]
GROUP BY date_bin_gapfill/date_bin(intervalExpr, time), expr...
[ ORDER BY ... ]

fill_func: locf|interpolate|coalesce

警告

influxql group by 只能为tag 或time(), 因此influxdb3 SQL 转换后也需要这样，不然转换后执行出错
influxql order by 只能time, 因此influxdb3 SQL 也限制这样，不然转换后执行出错
influxql 不能只查询tag, 因此influxdb3 SQL 也限制这样，不然转换后执行出错
influxql where 中不支持or, 因此influxdb3 SQL 也限制这样，不然转换后执行出错
influxql 对于float 类型会去除末尾的0，和influxdb3 SQL 结构不一致（不会去除）
格式1,2 不能对直接对查询列date_bin_gapfill返回的时间进行操作(date_bin 可以)，需要通过子查询实现
格式1,2 ，influxdb3和influxdb1 时间字段的值格式不同, 若需要一致需要使用格式3，套上to_char 函数转换格式为 '%Y-%m-%dT%H:%M:%S%.fZ' 或 '%Y-%m-%dT%H:%M:%S%.f%:z'
- 使用to_char 转换后的格式 %.f 表示9位精度小数，但influxdb1 中使用最短精度，会去掉末尾的0。仍可能不一致
格式3 可以对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 to_char(DATE_BIN(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 _time
order by _time

--- 格式2：
select to_char(DATE_BIN(INTERVAL 1 day, time) at time zone 'Asia/Shanghai', '%Y-%m-%dT%H:%M:%S%.f%:z'),
    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 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

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

--- 格式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 TZ('Asia/Shanghai');

--- 格式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 TZ('Asia/Shanghai');

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.2.1.2. 时区转换

描述

在INFLUXDB1中时区转换通过 tz(‘timezone’) 字句实现, 只影响time字段
- time 转时区是把time 当成utc 时区进行偏移转换
- group by 后使用tz 转时区，是把 group by 的time 当成没有时区的时间戳进行转换，直接加上时区，不对时间进行偏移
在INFLUXDB3 SQL中时区转换通过对表达式套上 tz(expr, 'timezone') 或 expr at time zone 'timezone' 实现
- time 是作为无时区的的时间戳
- tz 函数把时间当成utc 时间
- at time zone把时间当成无时区时间

语法

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

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

警告

按时间分区使用 at time zone
其他使用convert_tz/tz， convert_tz 为mysql 函数
INFLUXDB3 中time 是作为无时区的的时间戳，需要使用tz 函数（会把无时区的的时间戳当成utc 时间戳偏移）
INFLUXDB3 中若按时间分区需要使用at time zone（还是当做无时区的的时间戳）
convert_tz 转换为 INFLUXDB3 中的tz, influxdb1 中tz 子句，按时间分区是不应使用convert_tz，使用 at time zone

示例

-- 源SQL:
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='val5'
and time <= '1970-01-03T00:00:00Z'
and time >= '1970-01-01T00:00:00Z'
order by time

select to_char(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

select to_char(DATE_BIN(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 1
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');

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

1.3.6.2.1.3. interval 表达式

描述

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

语法

支持的INFLUXDB3 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 to_char(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 to_char(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 to_char(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.2.1.4. 正则比较过滤

描述

支持对regexp_like正则比较的转换

语法

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

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

警告

pattern 只能是字符串常量

示例

-- 源SQL:
select to_char(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 to_char(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 to_char(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');