0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.