Re: [GENERAL] Result of timestamp - timestamp

On 02/05/2017 01:21 PM, Thomas Kellerer wrote:
> Hello,
>
> I just stumbled about a report that has been running for a long time now
> and that relied on the fact that the interval "timestamp - timestamp"
> always returns an interval with days, hours, minutes. But never a
> "justified" interval with years, months, days and so on.

According to the docs:

https://www.postgresql.org/docs/9.6/static/functions-datetime.html

"Subtraction of date or timestamp values with the "-" operator returns
the number of days (24-hours) and hours/minutes/seconds between the
values, making the same adjustments."

It should always return days and hours.

>
> The query usees "extract(day from timestamp - timestamp)" which is
> working fine, but would apparently fail if a justified interval was
> returned

Did that happen?

>
> But I wonder if I'm relying on undocumented behaviour or if there is any
> situation where timestamp - timestamp would return a "justified" interval.
>
> So, my question is: will timestamp '2017-02-05 18:19:20' - timestamp
> '2016-11-18 23:00:00' always return "78 days 21:00:00"?
>
> Or is there any situation where the returned interval would be "2 mons
> 18 days 21:00:00" without using justiy_interval() on it.
>
> I couldn't find a clear statement on that in the manual.
>
> Thomas
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com