Re: [SQL] SQL to Check whether AN HOUR PERIOD is between start and end timestamps
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 d...@archonet.com To: Srikanth rss...@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 100608 | 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,
Re: [SQL] SQL to Check whether AN HOUR PERIOD is between start and end timestamps
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 Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL to Check whether AN HOUR PERIOD is between start and end timestamps
That did the job. Thanks. Am new to SQL, does not even know that there exists an Operator called OVERLAPS. Thanks Richard ../rssrik --- On Tue, 17/3/09, Richard Huxton d...@archonet.com wrote: From: Richard Huxton d...@archonet.com Subject: Re: [SQL] SQL to Check whether AN HOUR PERIOD is between start and end timestamps To: Srikanth rss...@yahoo.co.in Cc: pgsql-sql@postgresql.org Date: Tuesday, 17 March, 2009, 8:36 PM 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 100608 | 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. 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 Add more friends to your messenger and enjoy! Go to http://messenger.yahoo.com/invite/
Re: [SQL] SQL to Check whether AN HOUR PERIOD is between start and end timestamps
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 100608 | 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. 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
