CODEWITHSUNDEEP
postgresql
Luiz
Luiz

Reputation: 335

Subtracting two columns of type timestamp with time zone

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!

Upvotes: 1

Views: 1950

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246788

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.

Upvotes: 2

Related Questions