From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | James Kitambara <jameskitambara(at)yahoo(dot)co(dot)uk> |
Cc: | Srikanth <rssrik(at)yahoo(dot)co(dot)in>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps |
Date: | 2009-04-02 13:54:04 |
Message-ID: | 20090402135404.GD3163@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Emi Lu | 2009-04-02 14:01:53 | ibatis with overlaps query |
Previous Message | James Kitambara | 2009-04-02 12:31:13 | Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps |