用于从Postgres表中获取次日预报的SQL查询

Question

下表包含多行预测数据。天气预报每小时到达两次。该预测将包含7天的数据，每天24小时。我想要一种有效的方式来查询表，并且只获得14:10的预测(预测在每小时10 :45和45)，并且只在预测后的第二天(而不是额外的6天)

CREATE TABLE
    forecast_table
    (
        forecast_timestamp TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
        forecast_date DATE NOT NULL,
        forecast_hour INTEGER NOT NULL,
        number_value NUMERIC NOT NULL,
        PRIMARY KEY (forecast_timestamp, forecast_date , forecast_hour )
    );

例如，我想要2014年1月1日到2014年2月1日之间每天的所有24小时预报。这些必须是前一天14:10的预报。

我觉得必须有一种有效的方法来查询这个表，并且只获得我每天所需的24小时。

Answer 1

不清楚您在寻找什么，主要是因为您的表中有许多日期和时间，并且不清楚数据实际是什么样子，或者结果应该是什么样子。

您可以使用all kinds of date and time functions。

这应该列出时间为14:10的所有预测，发生在2014年1月，发布时意味着预测未来不到1天的日期。大约有十几种方法可以编写，您需要尝试找到一种好的方法。如果你能找到一种方法来编写它，让你的字段不在函数中，那就最好了。

SELECT *
FROM forecast_table
WHERE EXTRACT(HOUR FROM forecast_timestamp) = 14
    AND EXTRACT(MINUTE FROM forecast_timestamp) = 10
    AND forecast_timestamp > forecast_date - interval '1 day'  
    AND date_trunc('month', forecast_date) = timestamp '2014-01-01 00:00:00'

我不知道您是否希望将forecast_hours字段计入您希望应用于forecast_timestamp的24小时限制。如果您这样做了：

SELECT *
FROM forecast_table
WHERE EXTRACT(HOUR FROM forecast_timestamp) = 14
    AND EXTRACT(MINUTE FROM forecast_timestamp) = 10
    AND forecast_timestamp > (forecast_date + (forecast_hour * '1 hour'::interval)) - interval '1 day'  
    AND date_trunc('month', forecast_date) = timestamp '2014-01-01 00:00:00'

确实，forecast_hour字段的数据类型应该是interval hour而不是int。