TEIID访问VDB时WHERE子句时间戳转换报错排查

两个报错本质都是TEIID的SQL语法、函数体系和原生PostgreSQL不匹配导致的，不要直接把原生PG的SQL套到TEIID里执行：

• TEIID31100解析错误：格式串里\"T\"的转义写法TEIID解析器识别不了，字符串在双引号位置被提前截断，后面的格式片段被解析器当成了SQL关键字，直接报语法错。
• TEIID30068函数未知错误：默认配置下TEIID没有内置PostgreSQL的date_trunc函数，直接调用就会报函数不存在。

TEIID虽然对外提供PG兼容的连接协议，但解析层还是用自己的方言体系，时间格式规则参考Java的SimpleDateFormat，和PG的格式符规则、函数列表都有差异。

方案1：使用TEIID原生语法改写（无权限要求，推荐） #

TEIID本身支持直接把ISO8601格式的字符串强转为timestamp类型，不需要嵌套多层to_char、to_timestamp处理，直接做时间比较即可（时间类型的比较和字符串展示格式无关，只要类型正确就能准确比对），可直接执行的SQL如下：

SELECT col1, col2, col3 
FROM (EXEC my_Views.get_mytable('2022-01-01 00:00:00.000', now())) AS TAB 
WHERE my_date >= CAST('2022-06-15T08:27:00.599Z' AS timestamp)

如果你明确需要把时间截断到秒、丢弃毫秒部分，可以用TEIID内置的formattimestamp函数处理，注意TEIID的时间格式符不需要写PG风格的HH24，直接用HH代表24小时制即可：

SELECT col1, col2, col3 
FROM (EXEC my_Views.get_mytable('2022-01-01 00:00:00.000', now())) AS TAB 
WHERE my_date >= CAST(
    formattimestamp('yyyy-MM-dd HH:mm:ss', CAST('2022-06-15T08:27:00.599Z' AS timestamp)) 
    AS timestamp
)

方案2：开启PG兼容函数包（需VDB管理权限） #

如果你用的TEIID版本在8.0及以上，可以在VDB配置中导入PostgreSQL扩展函数包，开启后date_trunc、to_timestamp、to_char等PG常用函数就可以正常调用，不需要改写原有SQL：

• 编辑VDB配置文件，在vdb节点下添加PG函数源的导入配置
• 重载VDB配置或重启TEIID服务后，原有PG风格的SQL即可正常执行

注意：该方案需要有VDB的配置修改权限，没有管理权限的话优先选择方案1。

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