1

I'm converting some column types from TIMESTAMP WITHOUT TIME ZONE to TIMESTAMP WITH TIME ZONE.

Some functions that already exists in the system calculates the difference between two timestamps by simply doing something like: timestamp1 - timestamp2.

I'm wondering if this approach is correct when dealing with timezones. The solution I'm thinking of is the following:

timestamp1 at time zone 'America/Sao_Paulo' - timestamp2 at time zone 'America/Sao_Paulo'

But the question is: this approach handles DST changes correctly? It's safe to subtract timestamps with time zone this way?

Thanks!

Luiz
  • 325
  • 7
  • 28

1 Answers1

2

That is not safe. Consider this example:

SELECT TIMESTAMPTZ '2018-02-18 02:00:00+00' AT TIME ZONE 'America/Sao_Paulo'
     - TIMESTAMPTZ '2018-02-18 01:00:00+00' AT TIME ZONE 'America/Sao_Paulo';

 ?column? 
----------
 00:00:00
(1 row)

That is because these different absolute timestamps look the same in that time zone. Date arithmetic with timestamp without time zone just isn't correct, because the difference depends on the time zone.

To find the elapsed time between two timestamp with time zone values, simply subtract them:

SELECT TIMESTAMPTZ '2018-02-18 02:00:00+00'
     - TIMESTAMPTZ '2018-02-18 01:00:00+00';

 ?column? 
----------
 01:00:00
(1 row)

That result is always correct, because absolute time is used.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • But if I simply subtract them, how Postgres would know how long 1 day is? Because of DST changes 1 day may have 23 hours. This simply subtraction will cover that case? I don't understand how this could work. – Luiz May 08 '18 at 12:01
  • If you store `timestamp with time zone`, DST is trrelevant, because internally everything is done in UTC. Try `SET timezone = 'America/Sao_Paulo';` and then `SELECT TIMESTAMP '2018-02-19 00:00:00'::timestamptz - TIMESTAMP '2018-02-17 00:00:00'::timestamptz;`. And that's the correct result, because these two days took 49 hours. – Laurenz Albe May 08 '18 at 12:19
  • You are right! That works perfectly, thanks. But I still don't know how applying the timezone info does not work and when using UTC it takes into account days that have different hours. – Luiz May 08 '18 at 12:44
  • I tried to explain it in my answer. When you cast to `timestamp with time zone`, the current setting of `timezone` is used to compute what that timestamp means in absolute time. – Laurenz Albe May 08 '18 at 12:50
  • I'm thinking in this solution again. So when we subtract a TIMESTAMPTZ it uses the current session timezone to compute DST changes, is that right? This is basically the point of all my question, per your answer I still don't know how this subtraction will use 'America/Sao_Paulo' to compute the DST or any another tz of my choice (the costumer's tz, in that case). – Luiz May 11 '18 at 15:43
  • If you subtract two `timestamp with time zone` values, your session time zone does not come into play at all. Both values are stored in microseconds since `2000-01-01 00:00:00`, and PostgreSQL more or less just subtracts the values. – Laurenz Albe May 12 '18 at 02:38