Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps - Mailing list pgsql-sql
| From | James Kitambara |
|---|---|
| Subject | Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps |
| Date | |
| Msg-id | 849335.93192.qm@web27903.mail.ukl.yahoo.com Whole thread Raw |
| In response to | Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps (Richard Huxton <dev@archonet.com>) |
| Responses |
Re: SQL to Check whether "AN HOUR PERIOD" is between start
and end timestamps
|
| List | 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
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)
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,
The requirement is as follows,
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,
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.
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
-----------------------------
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
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,