SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
| От | Srikanth |
|---|---|
| Тема | SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps |
| Дата | |
| Msg-id | 19947.64230.qm@web94606.mail.in2.yahoo.com обсуждение исходный текст |
| Ответы |
Re: SQL to Check whether "AN HOUR PERIOD" is between start
and end timestamps
(Richard Huxton <dev@archonet.com>)
|
| Список | pgsql-sql |
<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">Dear all,<br /><br style="font-family:courier,monaco,monospace,sans-serif;" /><span style="font-family: courier,monaco,monospace,sans-serif;">Ihave a table that records User Login Sessions with two timestamp fields. BasicallyStart of Session and End of a Session (start_ts and end_ts). Each row in the table identifies a session which acustomer has used. </span><br style="font-family: courier,monaco,monospace,sans-serif;" /><br style="font-family: courier,monaco,monospace,sans-serif;"/><span style="font-family: courier,monaco,monospace,sans-serif;">Data from the table(session):</span><br style="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family: courier,monaco,monospace,sans-serif;">-----------------------------</span><brstyle="font-family: courier,monaco,monospace,sans-serif;"/><span style="font-family:courier,monaco,monospace,sans-serif;"> customer_id | log_session_id | start_ts | end_ts</span><br style="font-family: courier,monaco,monospace,sans-serif;"/><span style="font-family: courier,monaco,monospace,sans-serif;">-------------+-----------------+----------------------------+----------------------------</span><br style="font-family:courier,monaco,monospace,sans-serif;" /><span style="font-family: courier,monaco,monospace,sans-serif;"> 1006100716 | 209571229340224 | 15/12/2008 16:53:52.665327 | 15/12/2008 16:58:52.665327</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family: courier,monaco,monospace,sans-serif;"> 1006100789 | 112061228488202 | 05/12/2008 20:13:32.773065 | 09/12/2008 22:59:02.770218</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family: courier,monaco,monospace,sans-serif;"> 1006100888 | 214221233045949 | 27/01/2009 14:15:16.289626 | 27/01/2009 14:58:59.989182</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family: courier,monaco,monospace,sans-serif;"> 1006000008 | 205221236839534 | 12/03/2009 12:02:15.947509 | 12/03/2009 12:07:15.947509</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family: courier,monaco,monospace,sans-serif;"> 1006100825 | 112331229068049 | 12/12/2008 13:17:37.229249 | 12/12/2008 13:56:58.394577</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><br style="font-family: courier,monaco,monospace,sans-serif;"/><br style="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family:courier,monaco,monospace,sans-serif;">The requirement is as follows,</span><br style="font-family: courier,monaco,monospace,sans-serif;"/><br style="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family:courier,monaco,monospace,sans-serif;">I have to find out how many User Sessions that were present in anygiven "1 HOUR TIME PERIOD". A single User Session can span across many days.</span><br style="font-family: courier,monaco,monospace,sans-serif;"/><span style="font-family: courier,monaco,monospace,sans-serif;">Example:</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><spanstyle="font-family: courier,monaco,monospace,sans-serif;"> start_ts | end_ts</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family:courier,monaco,monospace,sans-serif;"> 05/12/2008 20:13:32.773065 | 09/12/2008 22:59:02.770218</span><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><br style="font-family: courier,monaco,monospace,sans-serif;"/><span style="font-family: courier,monaco,monospace,sans-serif;">-----------------------------------------------------------------------------------------------------</span><br style="font-family:courier,monaco,monospace,sans-serif;" /><br style="font-family: courier,monaco,monospace,sans-serif;"/><span style="font-family: courier,monaco,monospace,sans-serif;">Let me explain a scenario,</span><br style="font-family: courier,monaco,monospace,sans-serif;" /><br style="font-family: courier,monaco,monospace,sans-serif;"/><span style="font-family: courier,monaco,monospace,sans-serif;">I have to find outthe number of sessions during the interval '07/01/2009 11:00:00' to '07/01/2009 11:59:59'.</span><br style="font-family:courier,monaco,monospace,sans-serif;"/><br style="font-family: courier,monaco,monospace,sans-serif;" /><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family: courier,monaco,monospace,sans-serif;">IfI have to find the number of sessions present at any instant time say '07/01/200911:25:25', i can easily find out using the following Query, </span><br style="font-family: courier,monaco,monospace,sans-serif;"/><br style="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family:courier,monaco,monospace,sans-serif;">select count(log_session_id) from session where '07/01/2009 11:25:25'between start_ts and end_ts ;</span><br style="font-family: courier,monaco,monospace,sans-serif;" /><br style="font-family:courier,monaco,monospace,sans-serif;" /><span style="font-family: courier,monaco,monospace,sans-serif;">But,I have to find the number of sessions present during the "HOUR INTERVAL" '07/01/200911:00:00' to '07/01/2009 11:59:59'.</span><br style="font-family: courier,monaco,monospace,sans-serif;" /><brstyle="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family: courier,monaco,monospace,sans-serif;">Itried using wildcards in timestamp '07/01/2009 11:%:%" but in vain. I tries googling/ searching archives without any success either.</span><br style="font-family: courier,monaco,monospace,sans-serif;"/><br style="font-family: courier,monaco,monospace,sans-serif;" /><span style="font-family:courier,monaco,monospace,sans-serif;">I feel this is a general requirement and this topic should havealready been discussed.</span><br style="font-family: courier,monaco,monospace,sans-serif;" /><br style="font-family:courier,monaco,monospace,sans-serif;" /><span style="font-family: courier,monaco,monospace,sans-serif;">Couldsomeone help me solve this please ? Any lead would do, like some special postgres-functionor any other means.</span><br style="font-family: courier,monaco,monospace,sans-serif;" /><br style="font-family:courier,monaco,monospace,sans-serif;" /><span style="font-family: courier,monaco,monospace,sans-serif;">ManyThanks,</span><br style="font-family: courier,monaco,monospace,sans-serif;" /><spanstyle="font-family: courier,monaco,monospace,sans-serif;">./rssrik</span><br style="font-family: courier,monaco,monospace,sans-serif;"/><br style="font-family: courier,monaco,monospace,sans-serif;" /><br style="font-family:courier,monaco,monospace,sans-serif;" /></td></tr></table><br /><hr size="1" /> Add more friends to yourmessenger and enjoy! <a href="http://in.rd.yahoo.com/tagline_messenger_6/*http://messenger.yahoo.com/invite/"> Invitethem now.</a>