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 stackoverflow.com/questions/1964544/… — C8H10N4O2
– C8H10N4O2, Commented Jun 26, 2020 at 4:55
where updated_at <= current_timestamp - make_interval(hours => auto_archive_h) — user330315
– user330315, Commented Jun 26, 2020 at 4:59
OK, better read this question through carefully, then. stackoverflow.com/questions/1964544/… 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
– C8H10N4O2, Commented Jun 26, 2020 at 5: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
– Pavel, Commented Jun 26, 2020 at 6:06

Laurenz Albe · Accepted Answer · 2020-06-26 06:43:56Z

1

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)

answered Jun 26, 2020 at 6:43

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Calculate date different into query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related