How to compare timestamp with integer in Postgres?

Question

I want to have a trigger that would remove existing records where they have lived for more then 10 mins

drop table if exists authorization_code;
create table if not exists authorization_code(
    id int generated always as identity,
    created_at timestamptz not null
);

drop function if exists remove_expired();
create function remove_expired() 
   returns trigger 
   language plpgsql
as $$
begin
    --NOTE 10 mins as recommended by the OAuth2 spec
    delete from authorization_code where now() - created_at  > 600;
    return NEW;
end;
$$;

drop trigger if exists clean_up_expired_code on authorization_code;
create trigger clean_up_expired_code
  before insert
  on authorization_code
  for each row
  execute procedure remove_expired();

But right now if I insert, I would get an error like this:

sso=# insert into authorization_code(created_at) values(now());
ERROR:  operator does not exist: interval > integer
LINE 1: ...lete from authorization_code where now() - created_at  > 600
                                                                  ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY:  delete from authorization_code where now() - created_at  > 600
CONTEXT:  PL/pgSQL function remove_expired() line 4 at SQL statement

What is the correct way to achieve what I want?

timestamp - timestamp yields interval. So ... where now() - created_at > 600 * interval '1 second' or simply ... where now() - created_at > interval '10 minutes' — Stefanov.sm
– Stefanov.sm, Commented Oct 31, 2021 at 10:34

user330315 · Accepted Answer · 2021-10-31 11:07:23Z

2

The result of subtracting two timestamps is an interval so you can directly compare that:

where now() - created_at  > interval '10 minutes';

Or if you want to provide duration as e.g. a parameter indicating the number of seconds:

where now() - created_at  > make_interval(secs => 600);

answered Oct 31, 2021 at 11:07

user330315

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

Comments

Halley Oliveira · Accepted Answer · 2021-10-31 11:00:11Z

0

Try using this modified version of the remove_expired() function

halley=> \sf remove_expired
CREATE OR REPLACE FUNCTION public.remove_expired()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
    --NOTE 10 mins as recommended by the OAuth2 spec
    delete from authorization_code where EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)-EXTRACT(EPOCH FROM created_at)  > 600;
    return NEW;
end;
$function$

answered Oct 31, 2021 at 11:00

Halley Oliveira

3992 silver badges4 bronze badges

Collectives™ on Stack Overflow

How to compare timestamp with integer in Postgres?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related