From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: pgsql-sql@postgresql.org
Subject: Re: Re: SQL to Check whether "AN HOUR PERIOD" is between start and
	end timestamps
Date: Thu, 16 Apr 2009 12:21:01 +0200
User-Agent: KMail/1.11.2 (Linux/2.6.27-11-generic; KDE/4.2.2; i686; ; )
References: <19947.64230.qm@web94606.mail.in2.yahoo.com>
	<20090402135404.GD3163@alvh.no-ip.org>
	<grohvi$f4t$1@reversiblemaps.ath.cx>
In-Reply-To: <grohvi$f4t$1@reversiblemaps.ath.cx>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200904161221.01448.andreak@officenet.no>

On Saturday 11 April 2009 00:41:54 Jasen Betts wrote:
> On 2009-04-02, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> > James Kitambara wrote:
> >> Dear Srikanth,
> >> You can solve your problem by doing this
> >>=20
> >> THE SQL IS AS FOLLOWS
> >>   ASSUME TIME INTERVAL 2008-12-07 07:59:59 TO 2008-12-07 08:58:59 AND =
THE TABLE NAME time_interval
> >>=20
> >>  COUNT (*) FROM =20
> >>     (select customer_id, log_session_id, start_ts, end_ts , end_ts-sta=
rt_ts as "Interval" from time_interval
> >>      where end_ts-start_ts >=3D '1 hour'
> >>      and '2008-12-07 07:59:59' between start_ts and end_ts)
> >> AS COUNT ;
> >
> > Another way to phrase the WHERE clause is with the OVERLAPS operator,
> > something like this:
> >
> > WHERE (start_ts, end_ts) OVERLAPS ('2008-12-07 07:59:59', '2008-12-07 0=
8:59:59')
> >
> > What I'm not so sure about is how optimizable this construct is.
> >
>=20
> http://www.postgresql.org/docs/8.3/interactive/xindex.html
> if you gave the apropriate GIST index on (start_ts, end_ts) the
> overlaps may be optimisable. the subquery will run to completion=20
> and count will count the results. - but this form gives different results.
>=20
> beter to do=20
>=20
> select COUNT (*) AS COUNT FROM time_interval
>   WHERE (start_ts, end_ts) OVERLAPS ('2008-12-07 07:59:59', '2008-12-07 0=
8:59:59')
>=20
> or=20
> =20
> select COUNT (*) AS COUNT FROM time_interval
>   where end_ts-start_ts >=3D '1 hour'
>   and '2008-12-07 07:59:59' between start_ts and end_ts;

I only managed to get this to use the gist-index, and not with the overlaps=
 operator. I had to install the contrib-module btree_gist in order to be ab=
le to create a gist index on the timestamps.

This is my index:

CREATE index origo_tart_end_time_idx on onp_crm_activity_log using gist (st=
art_time, end_time) ;

start_time and end_time are both timestamps.

Here are the EXPLAIN outputs:

andreak=3D# EXPLAIN ANALYZE select id from onp_crm_activity_log where end_t=
ime - start_time >=3D '1 hour' AND '2008-12-07 07:59:59' between start_time=
 and end_time;
                                                                          Q=
UERY PLAN
=2D------------------------------------------------------------------------=
=2D------------------------------------------------------------------------=
=2D------------
 Bitmap Heap Scan on onp_crm_activity_log  (cost=3D10.56..232.62 rows=3D76 =
width=3D4) (actual time=3D0.175..0.175 rows=3D0 loops=3D1)
   Recheck Cond: (('2008-12-07 07:59:59'::timestamp without time zone >=3D =
start_time) AND ('2008-12-07 07:59:59'::timestamp without time zone <=3D en=
d_time))
   Filter: ((end_time - start_time) >=3D '01:00:00'::interval)
   ->  Bitmap Index Scan on origo_tart_end_time_idx  (cost=3D0.00..10.54 ro=
ws=3D229 width=3D0) (actual time=3D0.168..0.168 rows=3D0 loops=3D1)
         Index Cond: (('2008-12-07 07:59:59'::timestamp without time zone >=
=3D start_time) AND ('2008-12-07 07:59:59'::timestamp without time zone <=
=3D end_time))
 Total runtime: 0.274 ms
(6 rows)

andreak=3D# EXPLAIN ANALYZE select id from onp_crm_activity_log where (star=
t_time, end_time) OVERLAPS('2008-11-07 07:59:59'::timestamp, '2008-12-07 08=
:59:59'::timestamp);
                                                                     QUERY =
PLAN
=2D------------------------------------------------------------------------=
=2D------------------------------------------------------------------------=
=2D-
 Seq Scan on onp_crm_activity_log  (cost=3D0.00..319.29 rows=3D2968 width=
=3D4) (actual time=3D14.542..15.794 rows=3D83 loops=3D1)
   Filter: "overlaps"(start_time, end_time, '2008-11-07 07:59:59'::timestam=
p without time zone, '2008-12-07 08:59:59'::timestamp without time zone)
 Total runtime: 16.129 ms
(3 rows)

Is it possible to make the overlaps operator use the index? I'd prefer the =
overlaps-syntax as I find it cleaner.

=2D-=20
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CEO
=2D-----------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Rosenholmveien 25       | know how to do a thing and to watch         |
1414 Troll=C3=A5sen          | somebody else doing it wrong, without       |
NORWAY                  | comment.                                    |
                        |                                             |
Tlf:    +47 24 15 38 90 |                                             |
=46ax:    +47 24 15 38 91 |                                             |
Mobile: +47 909  56 963 |                                             |
=2D-----------------------+---------------------------------------------+