如何在Informix中为DATETIME列创建适配date()函数的索引

你遇到的-674错误是因为Informix不允许直接在函数索引中使用date()函数，需要改用Informix支持的、可被索引的日期转换表达式。以下是两种可行的解决方案：

方案一：直接创建基于日期转换表达式的函数索引 #

Informix支持将datetime类型通过CAST转换为DATE类型，或者用EXTEND函数截断到YEAR TO DAY（即日期部分），这两种表达式都可以用于创建函数索引：

方式1：使用CAST转换为DATE #

CREATE INDEX idx_pdt_gen_date ON patient_details_tbl(CAST(generated_date AS DATE)) ONLINE;

方式2：使用EXTEND截断日期部分 #

CREATE INDEX idx_pdt_gen_date ON patient_details_tbl(EXTEND(generated_date, YEAR TO DAY)) ONLINE;

创建索引后，需要调整查询语句的过滤条件，让它和索引的表达式完全匹配，这样查询优化器才能正确使用该索引：
对应CAST的查询：

SELECT 
    patient_id, patient_name 
FROM 
    patient_details_tbl 
WHERE 
    CAST(generated_date AS DATE) BETWEEN '2024-01-01' AND '2024-04-30';

对应EXTEND的查询：

SELECT 
    patient_id, patient_name 
FROM 
    patient_details_tbl 
WHERE 
    EXTEND(generated_date, YEAR TO DAY) BETWEEN '2024-01-01' AND '2024-04-30';

方案二：新增计算列并创建普通索引 #

如果不想修改原有查询语句，可以给表新增一个存储式计算列，专门存储日期部分，然后在这个列上创建普通索引：

1. 添加计算列：

ALTER TABLE patient_details_tbl ADD COLUMN gen_date DATE GENERATED ALWAYS AS (CAST(generated_date AS DATE)) STORED;

2. 创建索引：

CREATE INDEX idx_pdt_gen_date ON patient_details_tbl(gen_date) ONLINE;

之后你可以直接用这个新列进行查询，或者优化器会自动匹配原查询中的日期转换逻辑（建议统一使用新列以确保索引生效）。

注意：你的查询语句中写的是generated_time，但表结构里的列名是generated_date，需要先统一列名避免语法错误。

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