Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    5

    Unanswered: Chained datetime query

    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');

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    5
    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

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •