Обсуждение: [GENERAL] Result of timestamp - timestamp
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. The query usees "extract(day from timestamp - timestamp)" which is working fine, but would apparently fail if a justifiedinterval was returned But I wonder if I'm relying on undocumented behaviour or if there is any situation where timestamp - timestamp would returna "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
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
Adrian Klaver schrieb am 05.02.2017 um 22:40: >> 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/secondsbetween the values, making the same adjustments." > > It should always return days and hours. Ah, great. For some reason I didn't find that. >> 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? No it didn't. I just was curious if I relied on something that might change. Thanks