Thread: Calculating the difference between timetz values
Hello, I was looking for a way to get the difference (interval) between 2 timetz values, i.e.: postgres=# select '2:45+7'::timetz - '2:44+2'::timetz; ERROR: operator does not exist: time with time zone - time with time zone LINE 1: select '2:45+7'::timetz - '2:44+2'::timetz; I'd expect the result of the above to be the interval of 05:01:00. Is there any function or operator that calculates the difference correctly ? I've found a way to cast a timetz to the time without TZ and substract resulting time values, but it's not a correct solution for the problem above due to the loss of all TZ information. Regards, -- Alexey Klyukin http://www.CommandPrompt.com The PostgreSQL Company - Command Prompt, Inc.
On Jul 27, 2009, at 10:54 , Alexey Klyukin wrote: > Hello, > > I was looking for a way to get the difference (interval) between 2 > timetz values, i.e.: I don't have a solution, but am curious what your use case is for timetz (as opposed to timestamptz). Michael Glaesemann grzm seespotcode net
On Jul 27, 2009, at 6:52 PM, Michael Glaesemann wrote:
>
> On Jul 27, 2009, at 10:54 , Alexey Klyukin wrote:
>
>> Hello,
>>
>> I was looking for a way to get the difference (interval) between 2
>> timetz values, i.e.:
>
> 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.
Answering my own question, Alvaro proposed a solution with
extract(epoch from tz_value), the result is in seconds:
postgres=# select extract(epoch from '00:00:00+0'::timetz) -
extract(epoch from '2:00:00+2'::timetz);
?column?
----------
0
(1 row)
--
Alexey Klyukin http://www.CommandPrompt.com
The PostgreSQL Company - Command Prompt, Inc.
Alexey Klyukin <alexk@commandprompt.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.
regards, tom lane
Tom Lane wrote: > Alexey Klyukin <alexk@commandprompt.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.