how to compare two date column from two different tables by extracting specific parts in both columns using trigger/function in postgresql 10 I have written program something like below as per my question:
CREATE OR REPLACE FUNCTION hist_base_host_table_trig_func() RETURNS TRIGGER AS $hist_base_host_table_trig$
declare

username VARCHAR(40);


BEGIN
    --
    -- Create a row in emp_audit to reflect the operation performed on emp,
    -- make use of the special variable TG_OP to work out the operation.
    --
       IF (TG_OP = 'DELETE') THEN
    select userid into username 
    from gui_audit_trail 
    WHERE messageid='LS_REF_DATA_ROW_DELETED' 
        and description like '%BASE_HOST%' 
        and to_char(recorded,'YYYY-MM-DD HH:MI:SS')=to_char(OLD.last_update,'YYYY-MM-DD HH:MI:SS');
        RAISE NOTICE 'username is (%)',username;            
        INSERT INTO HIST_BASE_HOST SELECT OLD.zone_id, OLD.site_id, OLD.org_id, OLD.start_date, OLD.end_date, OLD.reserved_capacity, now(),'D', username;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO HIST_BASE_HOST 
           SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'U',user;
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO HIST_BASE_HOST SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'I',user;
        RETURN NEW;
    END IF;
    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$hist_base_host_table_trig$ LANGUAGE plpgsql;

CREATE TRIGGER hist_base_host_table_trig
AFTER INSERT OR UPDATE OR DELETE ON base_host
    FOR EACH ROW EXECUTE PROCEDURE hist_base_host_table_trig_func();

But &quot;username&quot; is not populating in output, expectation is &quot;administrator&quot; as output.
The two table data is as below:
select * from gui_audit_trail;
 id  |    userid     | moduleid  |    classid    |     typeid     |        messageid        |        recorded         
 718 | administrator | eventlink | CLASS IGNORED | METHOD IGNORED | LS_REF_DATA_ROW_DELETED | 2021-03-10 18:48:02.439

select * from hist_base_host;
 zone_id | site_id |   org_id   | start_date |  end_date  | reserved_capacity |        last_update         | chg_code |   user_id
       5 |       9 | PBTP       | 2002-01-01 | 2015-01-01 |              0.00 | 2021-03-10 18:48:02.413111 | D        |

in select query in above function, I want to cut time till &quot;2021-03-10 18:48:02&quot; from both columns recorded and last_update, but seems its not working, Is there any way to achieve the same functionality?
If you only want to remove milisecinds
CREATE OR REPLACE FUNCTION hist_base_host_table_trig_func() RETURNS TRIGGER AS $hist_base_host_table_trig$
declare

username VARCHAR(40);


BEGIN
    --
    -- Create a row in emp_audit to reflect the operation performed on emp,
    -- make use of the special variable TG_OP to work out the operation.
    --
       IF (TG_OP = 'DELETE') THEN
    select userid into username 
    from gui_audit_trail 
    WHERE messageid='LS_REF_DATA_ROW_DELETED' 
        and description like '%BASE_HOST%' 
        and date_trunc('second', recorded::timestamp)
        =date_trunc('second', OLD.last_update::timestamp);
        RAISE NOTICE 'username is (%)',username;            
        INSERT INTO HIST_BASE_HOST SELECT OLD.zone_id, OLD.site_id, OLD.org_id, OLD.start_date, OLD.end_date, OLD.reserved_capacity, now(),'D', username;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO HIST_BASE_HOST 
           SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'U',user;
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO HIST_BASE_HOST SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'I',user;
        RETURN NEW;
    END IF;
    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$hist_base_host_table_trig$ LANGUAGE plpgsql;

CREATE TRIGGER hist_base_host_table_trig
AFTER INSERT OR UPDATE OR DELETE ON base_host
    FOR EACH ROW EXECUTE PROCEDURE hist_base_host_table_trig_func();