CODEWITHSUNDEEP
postgresqltimestamp
Liondancer
Liondancer

Reputation: 16469

TIMESTAMP vs TIMESTAMPTZ correct value when taking time difference

For the query:

SELECT '2018-03-01'::TIMESTAMP - '2018-09-01'::TIMESTAMP, 
       '2018-03-01'::TIMESTAMPTZ - '2018-09-01'::TIMESTAMPTZ;

the values -184 days for TIMESTAMP and -183 days -23:00:00 for TIMESTAMPTZ is returned.

To me, -184 days "seems" correct but I was always told to use TIMESTAMPTZ. Which is the correct answer?

Upvotes: 11

Views: 20589

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

Answer posted as complement to comment discussion under OP's question.

Data type timestamptz is actually timestamp with time zone, while timestamp is timestamp without time zone.

The result that you get from substracting these specific dates like below is correct with 1 hour difference

SELECT '2018-03-01'::TIMESTAMPTZ - '2018-09-01'::TIMESTAMPTZ;

this is because 2018-03-01::timestamptz has +01 whereas 2018-09-01 has +02.

See it for yourself with this query

SELECT '2018-03-01'::TIMESTAMPTZ, '2018-09-01'::TIMESTAMPTZ

1 hour difference most likely comes from daylight savings and changing timezone over time.

In general I would always choose to include time zones within my system to properly handle time differences across the world and avoid issues that may arise from not having this. Client and server may have different timezones which can lead to problems.

Upvotes: 17

Related Questions