1.3.6.2.2. INFLUXBD3

1.3.6.2.2.1. 时区转换

描述

在INFLUXDB3 SQL中时区转换通过对表达式套上 tz(expr, 'timezone') 或 expr at time zone 'timezone' 实现
- time 是作为无时区的的时间戳
- tz 函数把时间当成utc 时间
- at time zone把时间当成无时区时间

语法

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

convert_tz(expr, 'UTC', 'timezone')
convert_tz(expr, '+00:00', 'timezone')

示例

-- 源SQL:
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='val1'


-- 目标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='val1';

1.3.6.2.2.2. 时间字符串加减时间间隔

描述

支持时间字符串直接加减时间间隔 - influxdb3 中不支持直接加减 - influxdb1 中支持直接加减

语法

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

expr + IntervalTimeExpr
IntervalTimeExpr + expr
expr - IntervalTimeExpr

警告

会对expr 套上to_timestamp, 但to_timestamp 只支持对如下格式时间的转换，因此不支持使用其他格式：
- ‘%Y-%m-%dT%H:%M:%S%.fZ’
- ‘%Y-%m-%dT%H:%M:%S%.f%:z’
- ‘%Y-%m-%d %H:%M:%S%.f’

示例

-- 源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' + interval '1 days'
order by time


-- 目标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>=to_timestamp('1970-01-01T00:00:00Z')+interval '1 days'
ORDER BY time;

1.3.6.2.2.3. interval 表达式

描述

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

语法

支持的格式如下：

interval 'num time_unit [num time_unit]...' -- 透传
interval num time_unit

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

警告

mysql 不支持nanosecond，millisecond，inflxudb 支持，因此扩充这两个类型

示例

-- 源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 3:20:30' day_second
and time >= '1970-01-01T00:00:00Z'
order by time

-- 目标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 day 3 hour 20 minute 30 second'
AND time>='1970-01-01T00:00:00Z'
ORDER BY time;