从information_schema.warehouse_load_history()过滤时间戳记录，查询返回空结果求助

可能的原因及修复方案 #

1. 优化WHERE子句的时间比较逻辑

你当前查询在WHERE中重复对字段做函数转换，既影响性能也容易因时区逻辑出错导致无结果。建议将epoch毫秒转为TIMESTAMP类型，直接与转换后的时间字段比较：

SELECT 
    DATE_PART(EPOCH_MILLISECOND, CONVERT_TIMEZONE('UTC', END_TIME)) AS end_time_epoch_ms,
    WAREHOUSE_NAME, 
    AVG_RUNNING, 
    AVG_QUEUED_LOAD, 
    AVG_QUEUED_PROVISIONING, 
    AVG_BLOCKED 
FROM TABLE(information_schema.warehouse_load_history()) 
WHERE CONVERT_TIMEZONE('UTC', END_TIME) BETWEEN 
    TO_TIMESTAMP(1668081337659::FLOAT/1000) 
    AND TO_TIMESTAMP(1668083015000::FLOAT/1000);

2. 先确认原始数据的时间范围与时区

执行简单查询查看END_TIME的实际值和时区转换结果，确保目标时间范围在数据覆盖内：

SELECT END_TIME, CONVERT_TIMEZONE('UTC', END_TIME) AS end_time_utc 
FROM TABLE(information_schema.warehouse_load_history())
LIMIT 10;

同时检查会话时区，避免时区差异导致筛选偏差：

SELECT CURRENT_TIMEZONE();

3. 处理超出默认数据周期的情况

warehouse_load_history()默认返回最近7天的数据，如果目标时间范围超出7天，必须显式指定时间范围参数：

SELECT 
    DATE_PART(EPOCH_MILLISECOND, CONVERT_TIMEZONE('UTC', END_TIME)) AS end_time_epoch_ms,
    WAREHOUSE_NAME, 
    AVG_RUNNING, 
    AVG_QUEUED_LOAD, 
    AVG_QUEUED_PROVISIONING, 
    AVG_BLOCKED 
FROM TABLE(information_schema.warehouse_load_history(
    DATE_RANGE_START => '2022-11-10 07:15:37'::TIMESTAMP_TZ,
    DATE_RANGE_END => '2022-11-10 07:43:35'::TIMESTAMP_TZ
))
WHERE CONVERT_TIMEZONE('UTC', END_TIME) BETWEEN 
    TO_TIMESTAMP(1668081337659::FLOAT/1000) 
    AND TO_TIMESTAMP(1668083015000::FLOAT/1000);

4. 验证目标时间段内的仓库活动

如果调整后仍无结果，说明该时间段内对应仓库没有负载记录，可以扩大时间范围或检查仓库运行状态。

内容的提问来源于stack exchange，提问作者Programming-Lover