Informix v12不规则时间序列表查询返回错误结果求助

环境信息 #

• 数据库版本：Informix v12
• 运行平台：Windows Server 2012 R2
• 问题现象：不规则时间序列表仅插入1条时间戳为2022-06-17 16:00:00的测试记录，查询时间戳等于2022-06-18 16:00:00的不存在记录时，错误返回时间戳为查询值的结果。

复现操作步骤 #

所有执行的SQL/命令如下：

1. 创建Dbspace

onspaces -c -d justtest_dbspace -p  E:\IBM\Informix\12.10\INFORMIX_DWH\dbspaces\ts_testTable.000 -o 30000 -s 30000

2. 创建时间序列行类型

create row type rw_justtest_row (
    timestamp datetime year to fraction(5),
    gas_code                VARCHAR(20),
    avg_concentration       FLOAT,
    standard_name           VARCHAR(50),
    threshold               FLOAT,
    modified_date           DATETIME year to second,
    text1                   VARCHAR(50),  -- 预留列
    text2                   VARCHAR(50), -- 预留列
    numeric1                FLOAT, -- 预留列
    numeric2                FLOAT  -- 预留列
);

3. 创建时间序列基础表

create table rw_justtest_table (
    station_id VARCHAR(10) NOT NULL,
    subdomain_id VARCHAR(20) NOT NULL,
    sensor_parameter_code VARCHAR(50) NOT NULL,
    period                  INTEGER NOT NULL,
    raw_9seconds_irr TIMESERIES(rw_justtest_row)
)
lock mode row;

4. 创建时间序列容器

execute procedure TSContainerCreate('container_justtest', 'justtest_dbspace', 'rw_justtest_row', 30000, 30000);

5. 插入时间序列日历

INSERT INTO CalendarTable(c_name, c_calendar)
VALUES('ts_1sec',
'startdate(2022-06-17 00:00:00.00000),pattern({1 on}, second)');

6. 创建虚拟表

execute procedure TSCreateVirtualTab('rw_justtest_table_v', 'rw_justtest_table', 
    'origin(2022-06-17 00:00:00.00000), calendar(ts_1sec), container(container_justtest), threshold(0), irregular');

7. 插入测试数据

insert into rw_justtest_table_v (station_id,subdomain_id,sensor_parameter_code, timestamp, gas_code, avg_concentration, standard_name, period, threshold) values
    ('YL', 'ABCDEFG','ABCDE','2022-06-17 16:00:00','ABCDE','0.222','FIGKL','60','0.09');

8. 验证查询（异常语句）

-- 计数查询（存在笔误，时间写为2002年）
select count(*) from informix.rw_justtest_table_v where timestamp >= '2002-06-17 16:00:00';
-- 正常查询，可返回插入的记录
select rowid,* from informix.rw_justtest_table_v where timestamp = '2022-06-17 16:00:00';
-- 异常查询，本应无返回，实际返回时间戳为2022-06-18 16:00:00的记录
select rowid,* from informix.rw_justtest_table_v where timestamp = '2022-06-18 16:00:00';

问题根因 #

• 核心原因1：创建虚拟表时未指定nointerpolate参数，Informix时间序列引擎默认开启隐式插值，查询不存在的时间点时会自动匹配/生成相邻时间点的记录，同时因为时间值未做显式精度转换，出现时间戳偏移。
• 核心原因2：所有插入、查询的时间字面量未显式指定为datetime year to fraction(5)类型，和行类型中timestamp字段的精度不匹配，导致时间序列内部基于origin的偏移量计算错误，出现固定24小时的时间偏移。
• 附加问题：计数查询中的时间条件笔误写为2002年，会导致计数结果校验不准；数值类字段插入时传入字符串值，依赖隐式转换也可能引发类型匹配异常。

修复方案 #

按以下步骤操作即可解决问题：

1. 删除原有测试用的虚拟表、基础表、容器、日历配置，清理测试数据。
2. 重建虚拟表时，在配置参数末尾添加nointerpolate，关闭隐式插值，禁止不存在的时间点返回生成的记录，修改后的创建语句如下：

execute procedure TSCreateVirtualTab('rw_justtest_table_v', 'rw_justtest_table', 
    'origin(2022-06-17 00:00:00.00000), calendar(ts_1sec), container(container_justtest), threshold(0), irregular, nointerpolate');

3. 所有涉及时间值、数值的插入、查询语句，显式使用对应字段要求的类型，禁止依赖数据库隐式转换，修改后的插入、查询语句示例：

-- 插入语句修正
insert into rw_justtest_table_v (station_id,subdomain_id,sensor_parameter_code, timestamp, gas_code, avg_concentration, standard_name, period, threshold) values
    ('YL', 'ABCDEFG','ABCDE',datetime(2022-06-17 16:00:00.00000) year to fraction(5),'ABCDE',0.222,'FIGKL',60,0.09);

-- 异常查询修正后验证（应返回0条）
select rowid,* from informix.rw_justtest_table_v where timestamp = datetime(2022-06-18 16:00:00.00000) year to fraction(5);

-- 计数查询修正笔误
select count(*) from informix.rw_justtest_table_v where timestamp >= datetime(2022-06-17 16:00:00.00000) year to fraction(5);

4. 额外校验：Windows平台下Informix服务可能出现时区读取异常，执行以下语句确认服务端时间无偏移：

select current year to fraction(5) as server_time from sysmaster:sysdual;

如果返回时间和本地实际时间差24小时，检查Informix服务的运行用户时区配置、SERVER_LOCALE环境变量设置即可。

内容的提问来源于stack exchange，提问作者Byl Hyh