PostgreSQL compare a timestamp with timezone with a timestamp

Question

How do you compare a timestamp with a timezone with a timestamp in postgresql? I am trying to cast one of the 2 to the type of the other but it does not work for me. Eg.

Having

TS1: 2019年01月07日 21:43:00

and

TS2: 2019年01月07日 20:43:37+01

I need to compare them and check if they are the same not including the seconds. So I have tried with:

select attimestamp, attimestamp_tz, attimestam=attimestamp_tz::timestamp from attendance where atid=1070

but this will always return false.

Answer 1

create table test (ts1 timestamp without time zone, ts2 timestamp with time zone)

✓

insert into test (ts1, ts2) 
VALUES ('2019-01-07 21:43:00', '2019-01-07 20:43:37+01'), 
 ('2019-01-07 21:43:00', '2019-01-07 20:43:37-01')

2 rows affected

select *, 
 to_char(ts1, 'YYYYMMDDHH24MI') str_ts1, 
 to_char(ts2, 'YYYYMMDDHH24MI') str_ts2, 
 to_char(ts1, 'YYYYMMDDHH24MI') = to_char(ts2, 'YYYYMMDDHH24MI') compare 
from test

ts1 | ts2 | str_ts1 | str_ts2 | compare
:------------------ | :--------------------- | :----------- | :----------- | :------
2019年01月07日 21:43:00 | 2019年01月07日 19:43:37+00 | 201901072143 | 201901071943 | f 
2019年01月07日 21:43:00 | 2019年01月07日 21:43:37+00 | 201901072143 | 201901072143 | t 

db<>fiddle here