timestamp - timestamp result

From: Thomas Kellerer <shammat(at)gmx(dot)net>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: timestamp - timestamp result
Date: 2020-06-26 05:28:43
Message-ID: c1696f68-fa8d-7759-6a9c-eb293ab1bbc9@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I regularly see people suggesting to use

extract(day from one_timestamp - other_timestamp)

to calculate the difference between two timestamps in days.

But I wonder if the "format" of the resulting interval is guaranteed to only have days
(and not months or years)

The following:

timestamp '2020-06-26 17:00:00' - timestamp '2019-04-01 14:00:00'

returns an interval like this:

0 years 0 mons 452 days 3 hours 0 mins 0.0 secs

However, is there ever a chance that the expression will yield the (equivalent) interval:

1 years 2 mons 25 days 3 hours 0 mins 0.0 secs

e.g. as the age() function does.

Is it safe to assume that "timestamp - timestamp" will never contain units larger then days?

Thomas

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2020-06-26 05:49:08 Re: timestamp - timestamp result
Previous Message Bhalodiya, Chirag 2020-06-26 03:00:04 Re: PostGreSQL TDE encryption patch