3

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?

2

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:

  • 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:

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

  • 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

  • Thank you! So the main thing you're telling me is: when I do timestamp BETWEEN timestamptz1 and timestamptz2 I actually do BETWEEN (timestamp1+offset1) AND (timestamp2+offcet2), because internally timestamps are stored and processed in UTC. Am I getting it right? – 7cart project Sep 8 '18 at 12:53
  • @dmytrocx75: To your 1st comment: Sounds about right. Not sure it was the "main thing", though. That would be: Timestamp and timezone handling depends on many things, is complex and may be confusing - currently more confusing than necessary. Dumb people have been allowed to set up dumb rules and standards. We have the misguided POSIX standard above and there are the moronic DST rules (which the EU might hopefully abolish soon) and other political nonsense complicating things. – Erwin Brandstetter Sep 8 '18 at 14:39

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.