How to compare timestamps with different time zones?

For example I have a table with timestamps:

2018-04-05 06:00:00 +01
    2018-04-05 06:00:00 +00
    2018-04-05 06:00:00 -01
    2018-04-05 04:00:00 -01

And I want to get entries that are ...

BETWEEN 2018-04-05 06:00:00+01 AND 2018-04-05 07:00:00+01

So my output would be:

2018-04-05 06:00:00 +01
    2018-04-05 04:00:00 -01

Will the operator BETWEEN handle time zone differences automatically and give me the needed output?

This just works:

SELECT *, ts AT TIME ZONE '-01'  -- see below about '-1' vs '+1'
FROM  (
   VALUES 
   (1, timestamptz '2018-04-05 06:00:00 +01')
 , (2,             '2018-04-05 06:00:00 +00')
 , (3,             '2018-04-05 06:00:00 -01')
 , (4,             '2018-04-05 04:00:00 -01')
   ) t(id, ts)
WHERE  ts BETWEEN '2018-04-05 06:00:00+01'  -- coerced to timestamptz
              AND '2018-04-05 07:00:00+01'; -- derived from context!

But note several pitfalls and potential misconceptions here!

-
The SQL construct BETWEEN ... AND (not a function, strictly speaking; more like an operator in practice) is not concerned with timezones. That would be a misconception of how things work.

-
Be aware of the two distinct data types timestamp (timestamp without time zone) and timestamptz (timestamp with time zone). See:

• Ignoring time zones altogether in Rails and PostgreSQL

-
In particular, the time zone offset in timestamptz literals only serves as input / output modifier to the value and is not stored at all. Only the according UTC time is stored internally. See:

• Time zone storage in data type “timestamp with time zone”

-
Be aware of the odd syntax difference between timestamp literals and time zone specifiers (flipped sign) due to disagreement between POSIX and SQL standards:

• Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?

-
The output you show (with different time zone offsets) cannot be achieved by simply returning timestamptz values:

2018-04-05 06:00:00 +01
2018-04-05 04:00:00 -01

timestamptz values are always displayed according to the timezone setting of the session. To get the strings you display (for varying time zones), you would have to preserve the time zone offset of the input and use it to format the output. (Or store complete input literals as text)

-
Finally, note that '2018-04-05 04:00:00 -01' also passes the test, since it's exactly the same timestamptz value as '2018-04-05 06:00:00+01', just formatted differently (same point in time, displayed for different time zones).
So three rows pass the test in your example, not just two.

If your head is spinning right now, consider this demo:

WITH tbl AS (
   SELECT *
        , split_part(tstz_string, ' ', 3) AS tz_string
        , tstz_string::timestamptz AS tstz
   FROM  (
      VALUES 
      (1, '2018-04-05 06:00:00 +01')
    , (2, '2018-04-05 06:00:00 +00')
    , (3, '2018-04-05 06:00:00 -01')
    , (4, '2018-04-05 04:00:00 -01')
      ) t(id, tstz_string)
   )
SELECT *
     , to_char(tz_string::numeric * -1, 'SG00')                                       AS tz_posix
     ,  tstz AT TIME ZONE (tz_string::numeric * -1)::text AS ts_at_org_tz
     , (tstz AT TIME ZONE (tz_string::numeric * -1)::text)::text || ' ' ||  tz_string AS tstz_org
     , (tstz AT TIME ZONE (tz_string::numeric * -1)::text)::text || ' ' ||  tz_string 
      = tstz_string                                                                   AS strings_equal
FROM   tbl;

id | tstz_string | tz_string | tstz | tz_posix | ts_at_org_tz | tstz_org | strings_equal
-: | :---------------------- | :-------- | :--------------------- | :------- | :------------------ | :---------------------- | :------------
1 | 2018-04-05 06:00:00 +01 | +01 | 2018-04-05 06:00:00+01 | -01 | 2018-04-05 06:00:00 | 2018-04-05 06:00:00 +01 | t
2 | 2018-04-05 06:00:00 +00 | +00 | 2018-04-05 07:00:00+01 | +00 | 2018-04-05 06:00:00 | 2018-04-05 06:00:00 +00 | t
3 | 2018-04-05 06:00:00 -01 | -01 | 2018-04-05 08:00:00+01 | +01 | 2018-04-05 06:00:00 | 2018-04-05 06:00:00 -01 | t
4 | 2018-04-05 04:00:00 -01 | -01 | 2018-04-05 06:00:00+01 | +01 | 2018-04-05 04:00:00 | 2018-04-05 04:00:00 -01 | t

db<>fiddle here

StackExchange Database Administrators Q#217078, answer score: 4