From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps |
Date: | 2009-04-10 22:41:54 |
Message-ID: | grohvi$f4t$1@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 2009-04-02, Alvaro Herrera <alvherre(at)commandprompt(dot)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;
From | Date | Subject | |
---|---|---|---|
Next Message | Rohit Suman | 2009-04-12 05:58:37 | ERROR: operator does not exist: date ~~ unknown |
Previous Message | Scott Marlowe | 2009-04-10 18:20:20 | Re: changing multiple pk's in one update |