pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
5+ messages / 5 participants
[nested] [flat]
* Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
@ 2009-04-02 12:31 James Kitambara <jameskitambara@yahoo.co.uk>
2009-04-02 13:54 ` Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 5+ messages in thread
From: James Kitambara @ 2009-04-02 12:31 UTC (permalink / raw)
To: Srikanth <rssrik@yahoo.co.in>; +Cc: pgsql-sql
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 ;
------------------------------------------ORGINAL MESSAGE--------------------------------------------------------------
________________________________
From: Richard Huxton <dev@archonet.com>
To: Srikanth <rssrik@yahoo.co.in>
Cc: pgsql-sql@postgresql.org
Sent: Tuesday, 17 March, 2009 18:06:09
Subject: Re: [SQL] SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
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 can span across many days.
Example:
start_ts | end_ts
05/12/2008 20:13:32.773065 | 09/12/2008 22:59:02.770218
-----------------------------------------------------------------------------------------------------
Let me explain a scenario,
I have to find out the number of sessions during the interval '07/01/2009 11:00:00' to '07/01/2009 11:59:59'.
If I have to find the number of sessions present at any instant time say '07/01/2009 11:25:25', i can easily find out using the following Query,
select count(log_session_id) from session where '07/01/2009 11:25:25' between start_ts and end_ts ;
But, I have to find the number of sessions present during the "HOUR INTERVAL" '07/01/2009 11:00:00' to '07/01/2009 11:59:59'.
I tried using wildcards in timestamp '07/01/2009 11:%:%" but in vain. I tries googling / searching archives without any success either.
I feel this is a general requirement and this topic should have already been discussed.
Could someone help me solve this please ? Any lead would do, like some special postgres-function or any other means.
Many Thanks,
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
2009-04-02 12:31 Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps James Kitambara <jameskitambara@yahoo.co.uk>
@ 2009-04-02 13:54 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-04-02 15:00 ` Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps Tom Lane <tgl@sss.pgh.pa.us>
2009-04-10 22:41 ` Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps Jasen Betts <jasen@xnet.co.nz>
0 siblings, 2 replies; 5+ messages in thread
From: Alvaro Herrera @ 2009-04-02 13:54 UTC (permalink / raw)
To: James Kitambara <jameskitambara@yahoo.co.uk>; +Cc: Srikanth <rssrik@yahoo.co.in>; 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
2009-04-02 12:31 Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps James Kitambara <jameskitambara@yahoo.co.uk>
2009-04-02 13:54 ` Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps Alvaro Herrera <alvherre@commandprompt.com>
@ 2009-04-02 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2009-04-02 15:00 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: James Kitambara <jameskitambara@yahoo.co.uk>; Srikanth <rssrik@yahoo.co.in>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
2009-04-02 12:31 Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps James Kitambara <jameskitambara@yahoo.co.uk>
2009-04-02 13:54 ` Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps Alvaro Herrera <alvherre@commandprompt.com>
@ 2009-04-10 22:41 ` Jasen Betts <jasen@xnet.co.nz>
2009-04-16 10:21 ` Re: Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps Andreas Joseph Krogh <andreak@officenet.no>
1 sibling, 1 reply; 5+ messages in thread
From: Jasen Betts @ 2009-04-10 22:41 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
2009-04-02 12:31 Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps James Kitambara <jameskitambara@yahoo.co.uk>
2009-04-02 13:54 ` Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps Alvaro Herrera <alvherre@commandprompt.com>
2009-04-10 22:41 ` Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps Jasen Betts <jasen@xnet.co.nz>
@ 2009-04-16 10:21 ` Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 0 replies; 5+ messages in thread
From: Andreas Joseph Krogh @ 2009-04-16 10:21 UTC (permalink / raw)
To: pgsql-sql
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-module btree_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 Heap Scan on onp_crm_activity_log (cost=10.56..232.62 rows=76 width=4) (actual time=0.175..0.175 rows=0 loops=1)
Recheck Cond: (('2008-12-07 07:59:59'::timestamp without time zone >= start_time) AND ('2008-12-07 07:59:59'::timestamp without time 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 time zone <= 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 Scan on 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'::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.
--
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 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-04-16 10:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-02 12:31 Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps James Kitambara <jameskitambara@yahoo.co.uk>
2009-04-02 13:54 ` Alvaro Herrera <alvherre@commandprompt.com>
2009-04-02 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-10 22:41 ` Jasen Betts <jasen@xnet.co.nz>
2009-04-16 10:21 ` Andreas Joseph Krogh <andreak@officenet.no>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox