Re: BUG #4972: RFE: convert timestamps to fractional seconds

On Monday 10 August 2009, Richard Neill wrote:
> * So, for example, to check whether two timestamps (ts1 and ts2) are less
> than 2.5 seconds apart, (returning boolean), I'd like to be able to do at
> least one of:
>
> abs(time(ts1 - ts2)) < 2.5
> #A "time" function converts timestamp to
> #sec.us since epoch)
>
> abs(cast (ts1 - ts2) as double) < 2.5
> #cast to double, might have to implicitly divide
> #by the unit of "1 second"
>
> (ts1 - ts2) / INTERVAL '1 second' < 2.5
> #Divide 2 dimensioned quantities to get
> #a dimensionless one.

What is wrong with (ts1-ts2) between i1 and i2:

cdrs=> select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc
(GCC) 4.1.2 (Gentoo 4.1.2)

cdrs=> select ('today 01:23:45'::timestamp - 'today 01:23:42.3'::timestamp)
between '-2.5 seconds'::interval and '2.5 seconds'::interval;
?column?
----------
f
(1 row)

cdrs=> select ('today 01:23:45'::timestamp - 'today 01:23:42.7'::timestamp)
between '-2.5 seconds'::interval and '2.5 seconds'::interval;
?column?
----------
t
(1 row)

cdrs=> select ('today 01:23:45'::timestamp - 'today 01:23:47.3'::timestamp)
between '-2.5 seconds'::interval and '2.5 seconds'::interval;
?column?
----------
t
(1 row)

cdrs=> select ('today 01:23:45'::timestamp - 'today 01:23:47.7'::timestamp)
between '-2.5 seconds'::interval and '2.5 seconds'::interval;
?column?
----------
f
(1 row)

> Currently, it's necessary to do something really really long-winded, eg:
> (ts1 - ts2 >= 0 AND ts1 - ts2 < interval '2.5 seconds') OR (ts2 - ts1 >= 0
> AND ts2 - ts1 < interval '2.5 seconds')

Not really, as you pointed out abs(interval) doesn't work for me, but a simple
between is easier than this, and intervals seem to support sign properly.

F.O.S.