Обсуждение: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps

<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">Dear all,<br /><br
style="font-family:courier,monaco,monospace,sans-serif;" /><span style="font-family:
courier,monaco,monospace,sans-serif;">Ihave a table that records User Login Sessions with two timestamp fields.
BasicallyStart of Session and End of a Session (start_ts and end_ts). Each row in the table identifies a session which
acustomer has used.  </span><br style="font-family: courier,monaco,monospace,sans-serif;" /><br style="font-family:
courier,monaco,monospace,sans-serif;"/><span style="font-family: courier,monaco,monospace,sans-serif;">Data from the
table(session):</span><br style="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family:
courier,monaco,monospace,sans-serif;">-----------------------------</span><brstyle="font-family:
courier,monaco,monospace,sans-serif;"/><span style="font-family:courier,monaco,monospace,sans-serif;"> customer_id |
log_session_id |          start_ts          |           end_ts</span><br style="font-family:
courier,monaco,monospace,sans-serif;"/><span style="font-family:
courier,monaco,monospace,sans-serif;">-------------+-----------------+----------------------------+----------------------------</span><br
style="font-family:courier,monaco,monospace,sans-serif;" /><span style="font-family:
courier,monaco,monospace,sans-serif;"> 1006100716 | 209571229340224 | 15/12/2008 16:53:52.665327 | 15/12/2008
16:58:52.665327</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family:
courier,monaco,monospace,sans-serif;"> 1006100789 | 112061228488202 | 05/12/2008 20:13:32.773065 | 09/12/2008
22:59:02.770218</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family:
courier,monaco,monospace,sans-serif;"> 1006100888 | 214221233045949 | 27/01/2009 14:15:16.289626 | 27/01/2009
14:58:59.989182</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family:
courier,monaco,monospace,sans-serif;"> 1006000008 | 205221236839534 | 12/03/2009 12:02:15.947509 | 12/03/2009
12:07:15.947509</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family:
courier,monaco,monospace,sans-serif;"> 1006100825 | 112331229068049 | 12/12/2008 13:17:37.229249 | 12/12/2008
13:56:58.394577</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><br style="font-family:
courier,monaco,monospace,sans-serif;"/><br style="font-family: courier,monaco,monospace,sans-serif;" /><span
style="font-family:courier,monaco,monospace,sans-serif;">The requirement is as follows,</span><br style="font-family:
courier,monaco,monospace,sans-serif;"/><br style="font-family: courier,monaco,monospace,sans-serif;" /><span
style="font-family:courier,monaco,monospace,sans-serif;">I have to find out how many User Sessions that were present in
anygiven "1 HOUR TIME PERIOD".  A single User Session can span across many days.</span><br style="font-family:
courier,monaco,monospace,sans-serif;"/><span style="font-family:
courier,monaco,monospace,sans-serif;">Example:</span><brstyle="font-family: courier,monaco,monospace,sans-serif;"
/><spanstyle="font-family: courier,monaco,monospace,sans-serif;">             start_ts          |          
end_ts</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span
style="font-family:courier,monaco,monospace,sans-serif;">   05/12/2008 20:13:32.773065 | 09/12/2008
22:59:02.770218</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><br style="font-family:
courier,monaco,monospace,sans-serif;"/><span style="font-family:
courier,monaco,monospace,sans-serif;">-----------------------------------------------------------------------------------------------------</span><br
style="font-family:courier,monaco,monospace,sans-serif;" /><br style="font-family:
courier,monaco,monospace,sans-serif;"/><span style="font-family: courier,monaco,monospace,sans-serif;">Let me explain a
scenario,</span><br style="font-family: courier,monaco,monospace,sans-serif;" /><br style="font-family:
courier,monaco,monospace,sans-serif;"/><span style="font-family: courier,monaco,monospace,sans-serif;">I have to find
outthe number of sessions during the interval '07/01/2009 11:00:00' to '07/01/2009 11:59:59'.</span><br
style="font-family:courier,monaco,monospace,sans-serif;"/><br style="font-family: courier,monaco,monospace,sans-serif;"
/><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family:
courier,monaco,monospace,sans-serif;">IfI have to find the number of sessions present at any instant time say
'07/01/200911:25:25', i can easily find out using the following Query, </span><br style="font-family:
courier,monaco,monospace,sans-serif;"/><br style="font-family: courier,monaco,monospace,sans-serif;" /><span
style="font-family:courier,monaco,monospace,sans-serif;">select count(log_session_id) from session where '07/01/2009
11:25:25'between start_ts and end_ts ;</span><br style="font-family: courier,monaco,monospace,sans-serif;" /><br
style="font-family:courier,monaco,monospace,sans-serif;" /><span style="font-family:
courier,monaco,monospace,sans-serif;">But,I have to find the number of sessions present during the "HOUR INTERVAL"
'07/01/200911:00:00' to '07/01/2009 11:59:59'.</span><br style="font-family: courier,monaco,monospace,sans-serif;"
/><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family:
courier,monaco,monospace,sans-serif;">Itried using wildcards in timestamp '07/01/2009 11:%:%" but in vain. I tries
googling/ searching archives without any success either.</span><br style="font-family:
courier,monaco,monospace,sans-serif;"/><br style="font-family: courier,monaco,monospace,sans-serif;" /><span
style="font-family:courier,monaco,monospace,sans-serif;">I feel this is a general requirement and this topic should
havealready been discussed.</span><br style="font-family: courier,monaco,monospace,sans-serif;" /><br
style="font-family:courier,monaco,monospace,sans-serif;" /><span style="font-family:
courier,monaco,monospace,sans-serif;">Couldsomeone help me solve this please ?  Any lead would do, like some special
postgres-functionor any other means.</span><br style="font-family: courier,monaco,monospace,sans-serif;" /><br
style="font-family:courier,monaco,monospace,sans-serif;" /><span style="font-family:
courier,monaco,monospace,sans-serif;">ManyThanks,</span><br style="font-family: courier,monaco,monospace,sans-serif;"
/><spanstyle="font-family: courier,monaco,monospace,sans-serif;">./rssrik</span><br style="font-family:
courier,monaco,monospace,sans-serif;"/><br style="font-family: courier,monaco,monospace,sans-serif;" /><br
style="font-family:courier,monaco,monospace,sans-serif;" /></td></tr></table><br /><hr size="1" /> Add more friends to
yourmessenger and enjoy! <a href="http://in.rd.yahoo.com/tagline_messenger_6/*http://messenger.yahoo.com/invite/">
Invitethem now.</a> 

Srikanth wrote:
> Dear all,
> 
> I have a table that records User Login Sessions with two timestamp fields. Basically Start of Session and End of a
Session(start_ts and end_ts). Each row in the table identifies a session which a customer has used.  
 
> 
> Data from the table (session):
> -----------------------------
>  customer_id | log_session_id  |          start_ts          |           end_ts
> -------------+-----------------+----------------------------+----------------------------
>  1006100716  | 209571229340224 | 15/12/2008 16:53:52.665327 | 15/12/2008 16:58:52.665327
>  1006100789  | 112061228488202 | 05/12/2008 20:13:32.773065 | 09/12/2008 22:59:02.770218
>  1006100888  | 214221233045949 | 27/01/2009 14:15:16.289626 | 27/01/2009 14:58:59.989182
>  1006000008  | 205221236839534 | 12/03/2009 12:02:15.947509 | 12/03/2009 12:07:15.947509
>  1006100825  | 112331229068049 | 12/12/2008 13:17:37.229249 | 12/12/2008 13:56:58.394577
> 
> 
> The requirement is as follows,
> 
> I have to find out how many User Sessions that were present in any given "1 HOUR TIME PERIOD".  A single User Session
canspan across many days.
 

SELECT * FROM session WHERE (start_ts,end_ts) OVERLAPS (<start-of-hour>,
<end-of-hour>);

> I tried using wildcards in timestamp '07/01/2009 11:%:%" but in vain.
I tries googling / searching archives without any success either.

I'd have thought OVERLAPS would be mentioned in the date/time handling
sections of the manual.

--  Richard Huxton Archonet Ltd

<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">That did the job.
Thanks.<br/>Am new to SQL, does not even know that there exists an Operator called OVERLAPS.<br /><br />Thanks Richard
<br/>./rssrik<br />--- On <b>Tue, 17/3/09, Richard Huxton <i><dev@archonet.com></i></b> wrote:<br /><blockquote
style="border-left:2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"><br />From: Richard Huxton
<dev@archonet.com><br/>Subject: Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end
timestamps<br/>To: "Srikanth" <rssrik@yahoo.co.in><br />Cc: pgsql-sql@postgresql.org<br />Date: Tuesday, 17
March,2009, 8:36 PM<br /><br /><div class="plainMail">Srikanth wrote:<br />> Dear all,<br />> <br />> I have a
tablethat records User Login Sessions with two timestamp fields. Basically Start of Session and End of a Session
(start_tsand end_ts). Each row in the table identifies a session which a customer has used.  <br />> <br />> Data
fromthe table (session):<br />> -----------------------------<br />>  customer_id | log_session_id  |         
start_ts         |           end_ts<br />>
-------------+-----------------+----------------------------+----------------------------<br/>>  1006100716  |
209571229340224| 15/12/2008 16:53:52.665327 | 15/12/2008 16:58:52.665327<br />>  1006100789  | 112061228488202 |
05/12/200820:13:32.773065 | 09/12/2008 22:59:02.770218<br />>  1006100888  | 214221233045949 | 27/01/2009
14:15:16.289626| 27/01/2009 14:58:59.989182<br />>  1006000008  | 205221236839534 | 12/03/2009 12:02:15.947509 |
12/03/200912:07:15.947509<br />>  1006100825  | 112331229068049 | 12/12/2008 13:17:37.229249 | 12/12/2008
13:56:58.394577<br/>> <br />> <br />> The requirement is as follows,<br />> <br />> I have to find out
howmany User Sessions that were present in any given "1 HOUR TIME PERIOD".  A single User Session can span across many
days.<br/><br />SELECT * FROM session WHERE (start_ts,end_ts) OVERLAPS (<start-of-hour>,<br
/><end-of-hour>);<br/><br />> I tried using wildcards in timestamp '07/01/2009 11:%:%" but in vain.<br />I
triesgoogling / searching archives without any success either.<br /><br />I'd have thought OVERLAPS would be mentioned
inthe date/time handling<br />sections of the manual.<br /><br />-- <br />  Richard Huxton<br />  Archonet Ltd<br
/></div></blockquote></td></tr></table><br/><hr size="1" /> Add more friends to your messenger and enjoy! <a
href="http://in.rd.yahoo.com/tagline_messenger_6/*http://messenger.yahoo.com/invite/">Invite them now.</a> 

On 2009-03-17, Srikanth <rssrik@yahoo.co.in> wrote:

> Dear all,
>
> I have a table that records User Login Sessions with two timestamp fields. =
> Basically Start of Session and End of a Session (start_ts and end_ts). Each=
>  row in the table identifies a session which a customer has used.=A0=20
>
>
> I have to find out how many User Sessions that were present in any given "1=
>  HOUR TIME PERIOD".=A0 A single User Session can span across many days.

select count(*) from session WHERE start_ts < TIME + '1 hour'::intervalAND   end_ts >= TIME;
(replace both ocurrences of TIME with the time the interval starts)

James Kitambara wrote:
> Dear Srikanth,
> You can solve your problem by doing this
> 
> 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
> 
>  COUNT (*) FROM  
>     (select customer_id, log_session_id, start_ts, end_ts , end_ts-start_ts as "Interval" from time_interval
>      where end_ts-start_ts >= '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 08:59:59')

What I'm not so sure about is how optimizable this construct is.

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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> 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 08:59:59')

> What I'm not so sure about is how optimizable this construct is.

Not at all :-(  --- or at least, our code doesn't do anything with
it currrently; I shouldn't claim that it's impossible to optimize.
        regards, tom lane

On 2009-04-02, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> James Kitambara wrote:
>> Dear Srikanth,
>> You can solve your problem by doing this
>> 
>> 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
>> 
>>  COUNT (*) FROM  
>>     (select customer_id, log_session_id, start_ts, end_ts , end_ts-start_ts as "Interval" from time_interval
>>      where end_ts-start_ts >= '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 08:59:59')
>
> What I'm not so sure about is how optimizable this construct is.
>

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 
and count will count the results. - but this form gives different results.

beter to do 

select COUNT (*) AS COUNT FROM time_interval WHERE (start_ts, end_ts) OVERLAPS ('2008-12-07 07:59:59', '2008-12-07
08:59:59')

or 
select COUNT (*) AS COUNT FROM time_interval where end_ts-start_ts >= '1 hour' and '2008-12-07 07:59:59' between
start_tsand end_ts;
 

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
> >>
> >> 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
> >>
> >>  COUNT (*) FROM
> >>     (select customer_id, log_session_id, start_ts, end_ts , end_ts-start_ts as "Interval" from time_interval
> >>      where end_ts-start_ts >= '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 08:59:59')
> >
> > What I'm not so sure about is how optimizable this construct is.
> >
>
> 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
> and count will count the results. - but this form gives different results.
>
> beter to do
>
> select COUNT (*) AS COUNT FROM time_interval
>   WHERE (start_ts, end_ts) OVERLAPS ('2008-12-07 07:59:59', '2008-12-07 08:59:59')
>
> or
>
> select COUNT (*) AS COUNT FROM time_interval
>   where end_ts-start_ts >= '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-modulebtree_gist in order to be able 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 (start_time, end_time) ;

start_time and end_time are both timestamps.

Here are the EXPLAIN outputs:

andreak=# EXPLAIN ANALYZE select id from onp_crm_activity_log where end_time - start_time >= '1 hour' AND '2008-12-07
07:59:59'between start_time and end_time;                                                                         QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------Bitmap
HeapScan on onp_crm_activity_log  (cost=10.56..232.62 rows=76 width=4) (actual time=0.175..0.175 rows=0 loops=1)
RecheckCond: (('2008-12-07 07:59:59'::timestamp without time zone >= start_time) AND ('2008-12-07 07:59:59'::timestamp
withouttime zone <= end_time))  Filter: ((end_time - start_time) >= '01:00:00'::interval)  ->  Bitmap Index Scan on
origo_tart_end_time_idx (cost=0.00..10.54 rows=229 width=0) (actual time=0.168..0.168 rows=0 loops=1)        Index
Cond:(('2008-12-07 07:59:59'::timestamp without time zone >= start_time) AND ('2008-12-07 07:59:59'::timestamp without
timezone <= end_time))Total runtime: 0.274 ms 
(6 rows)

andreak=# EXPLAIN ANALYZE select id from onp_crm_activity_log where (start_time, end_time) OVERLAPS('2008-11-07
07:59:59'::timestamp,'2008-12-07 08:59:59'::timestamp);
  QUERY PLAN 

----------------------------------------------------------------------------------------------------------------------------------------------------Seq
Scanon onp_crm_activity_log  (cost=0.00..319.29 rows=2968 width=4) (actual time=14.542..15.794 rows=83 loops=1)
Filter:"overlaps"(start_time, end_time, '2008-11-07 07:59:59'::timestamp without time zone, '2008-12-07
08:59:59'::timestampwithout 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.

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