Chained datetime query

FBChris · #1 (**permalink**) 07-30-09, 02:13

I'm looking for a solution to make a From Until chain from a set of records which have just one datetime.

The database table looks like this...

Code:

1	3232235778	test.php	2009-07-22 10:00:00
2	3232235778	page2.php	2009-07-22 11:00:00
3	3232235777	test.php	2009-07-22 10:30:00
4	3232235777	test.php	2009-07-22 11:15:00
5	3232235777	page2.php	2009-07-22 12:15:00
6	3232235778	page2.php	2009-07-22 12:00:00

What I'd like to do is a query like "Give me all IP in a given period with their start and end time"

If I want everyone that acces test.php between 07-22 09:00:00 and 07-22 11:00:00 it should return

Code:

3232235778	test.php	2009-07-22 10:00:00	2009-07-22 11:00:00
3232235777	test.php	2009-07-22 10:30:00	2009-07-22 12:15:00

So what the query needs to have is a way to make it kind of a chain from the datetime in the table.
From-Until / From-Until / From-Until etc. I also need to look around the border constraint to find the actual begin time.

It can be someone started accessing the page before the period I queried. The next page accessed is implicit the until time from the page visit

Code:

-- ----------------------------
-- Table structure for pagehits
-- ----------------------------
DROP TABLE IF EXISTS `pagehits`;
CREATE TABLE `pagehits` (
  `PAGEHITS_ID` int(10) unsigned NOT NULL auto_increment,
  `IP` int(10) unsigned default NULL,
  `page` varchar(255) collate latin1_general_ci NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY  (`PAGEHITS_ID`)
);

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `pagehits` VALUES ('1', '3232235778', 'test.php', '2009-07-22 10:00:00');
INSERT INTO `pagehits` VALUES ('2', '3232235778', 'page2.php', '2009-07-22 11:00:00');
INSERT INTO `pagehits` VALUES ('3', '3232235777', 'test.php', '2009-07-22 10:30:00');
INSERT INTO `pagehits` VALUES ('4', '3232235777', 'test.php', '2009-07-22 11:15:00');
INSERT INTO `pagehits` VALUES ('5', '3232235777', 'page2.php', '2009-07-22 12:15:00');
INSERT INTO `pagehits` VALUES ('6', '3232235778', 'page2.php', '2009-07-22 12:00:00');

r937 · #2 (**permalink**) 07-30-09, 07:03

Quote:

Originally Posted by FBChris

What I'd like to do is a query like "Give me all IP in a given period with their start and end time"

this request is simple

the bit about "chaining" isn't -- what exactly would "chaining" do for you anyway?

if you want only those From-Until datetime ranges which overlap the range you specify, then it's easy

consider this timeline --

Code:

                Begin           End            
                  |              |               
1    From---Until |              |               
                  |              |               
2          From---|---Until      |               
                  |              |               
3                 | From---Until |               
                  |              |               
4          From---|--------------|---Until     
                  |              |               
5                 |       From---|---Until
                  |              |               
6                 |              |  From---Until

so you enter a Begin-End range, and you want the query to return all From-Until periods except #1 and #6, correct?

Code:

... WHERE Until >= Begin  /* eliminates case 1 */
      AND From  <= End    /* eliminates case 6 */

simple

FBChris · #3 (**permalink**) 07-30-09, 12:50

Maybe the word chaining is wrong, sorry for that.

What I meant by chaining is the fact that each entry is stored in a single record, while the next record for the same user tells whether or not it is the end time.

I agree on the fact it would be easy if the records would have a From and Until datetime.

So maybe I should redirect the question in how to get those results sorted in a way the record looks like:

Code:

IP - Page - From - Until

dav1mo · #4 (**permalink**) 07-31-09, 11:30

how about joining the table to itself? something like:
select my_cols from my_table1 a, my_table1 b
where ...
and b.time = (select min(c.time) from my_table1 c
where ...
and c.time > a.time)

Dave

Internet Services

Web Development

Digital Marketing

Consumer Tech