PostgreSQL Calculate date different into query

Question

Try to write the query for each item should be archived if N hours (auto_archive_h in the table) wasn't updated. For mark item as archived need set archived to TURE and archived_at to now. But if auto_archive_h is NULL or updated_at is NULL not touching in the query, just pass.

Saturday and Sunday do not count as time for archived.

I got an issue in the WHERE condition. How to calculate the difference in hours and compare it with an auto_archive_h?

Table:

CREATE TABLE items
(
    id             BIGSERIAL PRIMARY KEY,
    updated_at     TIMESTAMP,
    auto_archive_h INT,
    archived       BOOLEAN        NOT NULL DEFAULT FALSE,
    archived_at    TIMESTAMP
);

Query:

UPDATE items
SET archived = TRUE, archived_at = now()
WHERE ?issue_here?

That something like to_hours(now() - updated_at) > auto_archive_h but with wrire syntax.

Help me to complete the query. Thank You.

Can you clarify: will you have daylight savings time situations? If not, meaning every day for this system is 24 hours, just use inverval. See also this question https://stackoverflow.com/questions/1964544/timestamp-difference-in-hours-for-postgresql — C8H10N4O2, Jun 26 '20 at 04:55
`where updated_at <= current_timestamp - make_interval(hours => auto_archive_h)` — , Jun 26 '20 at 04:59
OK, better read this question through carefully, then. https://stackoverflow.com/questions/1964544/timestamp-difference-in-hours-for-postgresql When you lose or gain an hour due to the change, do you want that hour to count towards the required archive interval or not? — C8H10N4O2, Jun 26 '20 at 05:08
@Laurenz Albe but I don't know item will be expired before weekend or after. Perhaps the user set 1 hour on Monday. That can be a good idea if a set minimum of 1 week for `auto_archive_h`. Or I misunderstand your idea. — Pavel, Jun 26 '20 at 06:06

score 1 · Accepted Answer · answered Jun 26 '20 at 06:43

Perhaps it is easiest with a custom function:

CREATE FUNCTION ago_excl_weekend(
   p_hours integer
) RETURNS timestamp
   LANGUAGE sql AS
$$SELECT localtimestamp - make_interval(hours => p_hours) -
       (extract(week FROM localtimestamp) -
        extract(week FROM localtimestamp -
                          make_interval(
                             hours => p_hours + p_hours / 168 * 48
                          )
               )
       )
     * INTERVAL '48' HOUR$$;

Then the condition would be

WHERE updated_at <= ago_excl_weekend(auto_archive_h)

PostgreSQL Calculate date different into query

1 Answers1