sql - Postgres : Don't run a query if current time is in range

Question

我有一个查询需要每小时运行一次，以便在查询返回任何 数据时发送警报。为避免在下类时间收到更改，如果当前时间在美国东部时间上午 12 点到早上 7 点之间，我不希望运行此查询。

这是 SQL 在其当前状态下的样子:

SELECT * FROM (
    SELECT FLOOR(EXTRACT(EPOCH FROM (now() - updated_at))/60) :: INT as minutes_since_update
    FROM table_a
        WHERE column_a IS TRUE AND column_b IS NULL
    LIMIT 1) a
WHERE minutes_since_update > 60

如何更改此查询，使其不会在下类时间运行。

我已经试过了，但我不清楚为什么它不起作用。

SELECT * FROM (
    IF (SELECT CURRENT_TIME BETWEEN '04:00:00' AND '11:00:00') THEN
        SELECT 0 as minutes_since_update FROM table_a where true = false;
    ELSE
        SELECT FLOOR(EXTRACT(EPOCH FROM (now() - updated_at))/60) :: INT as minutes_since_update
        FROM table_a
            WHERE column_a IS TRUE AND column_b IS NULL
        LIMIT 1;
    END IF;
) a
WHERE minutes_since_update > 60

Answer 1

只需将子句放在 WHERE 条件中即可。

SELECT FLOOR(EXTRACT(EPOCH FROM (now() - updated_at))/60) :: INT as minutes_since_update
FROM table_a
WHERE column_a IS TRUE AND column_b IS NULL
AND CURRENT_TIME BETWEEN '00:00:00' AND '07:00:00'
LIMIT 1;

但是，我可能会使用类似 pg_cron 的东西