Re: Calculating the difference between timetz values

Tom Lane wrote:
> Alexey Klyukin <alexk(at)commandprompt(dot)com> writes:
> > On Jul 27, 2009, at 6:52 PM, Michael Glaesemann wrote:
> >> I don't have a solution, but am curious what your use case is for
> >> timetz (as opposed to timestamptz).
>
> > I'm writing a custom trigger function that has to compare values of
> > time* types and make some actions depending on a result.
>
> It's still fairly unclear why you think that comparing timetz values
> is a useful activity. Is "23:32" earlier or later than "00:32"?
> How can you tell whether it's the same day or different days? Adding
> timezones into that doesn't make it better.
>
> Our documentation deprecates timetz as a poorly-defined datatype,
> and I've never seen a reason to argue with that judgment. I'd suggest
> taking a very hard look at why you're not using timestamptz instead.

Yeah, well, this is a customer problem, so we're providing a solution to
the problem they presented us. The underlying problem is Ruby on Rails
doing something silly updating timestamps more often than some small
number of milliseconds (or something like that), so what we want is to
prevent such an update from happening. The problem being presented is
not 23:32 > 00:32 but rather 23:32:23.0001 > 23:32:23.00012. On the
border condition that 23:59:59.99999 > 00:00:00.00000 (which is
obviously ambiguous) we just avoid the question by doing the update
always.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.