Re: Incorrect handling of timezones with extract

> The behavior of timestamp-plus-interval is certainly supported by that
> argument, but I'm less convinced about timestamp-minus-timestamp. The
> raw result of the timestamp subtraction here is 71 hours (not the normal
> 72). Perhaps it should be outputting it that way instead of converting
> to "2 days 23 hours", which is arguably inaccurate.

Probably, yes. We added a barrier so that interval is supposed to be
years-months | weeks-days | hours-minutes-seconds-ms-ns. However, it
sounds like we missed a few cases.

Mind you, there's a kind of insurmountable issue with timestamp
subtraction, where you can come up with *lots* of cases where timestamp
subtraction is not reversable due to the way we handle intervalization.
Realistically, we'd need to have something like a ts_sub( tstz, tstz,
interval ) function to have a 100% reversible option. That probably
contributes to why Oracle has never supported timestamp - timestamp.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com