How to compare timestamp with integer in Postgres?-CodePudding

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?

CodePudding user response：

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$

CodePudding user response：

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);