SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Between set times?

    Hello,

    Wondering if anyone can help as i'm really confused. I want to run two dynamic queries in the same format for half an hour time slots to output all records in the database where the start time is between:

    - 12.00pm and 12.30pm.
    and
    - 12.30pm and 13.00pm.

    So I have a record in my database where the estimated_start_time is 11:45pm and estimated_end_time is 12:45pm

    Now if I run the query I have written for between 12.00pm and 12.30pm a result is returned:

    Code:
    SELECT * FROM `reps_schedule` WHERE estimated_start_time <= '2011-01-10 12:00:00' AND estimated_end_time >= '2011-01-10 12:30:00' AND user_id = 12
    But that same style query it would not run for my 12.30pm and 13.00pm?

    Code:
    SELECT * FROM `reps_schedule` WHERE estimated_start_time <= '2011-01-10 12:30:00' AND estimated_end_time >= '2011-01-10 13:00:00' AND user_id = 12
    Anyone kindly help?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by chris_stfc View Post
    But that same style query it would not run for my 12.30pm and 13.00pm?
    would not run? really?

    what error message did you get?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, when I say will not run I mean returns no results, even though a record shows the estimated_end_time is 11:45:00 and estimated_end_time is 12:45:00

    Code:
    SELECT * FROM `reps_schedule` WHERE estimated_start_time <= '2011-01-10 12:30:00' AND estimated_end_time >= '2011-01-10 13:00:00' AND user_id = 12
    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you do a SHOW CREATE TABLE for this table please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, Thanks for this. Is this what you need?

    Code:
    CREATE TABLE IF NOT EXISTS `reps_schedule` (
      `schedule_id` int(8) NOT NULL auto_increment,
      `user_id` int(4) NOT NULL,
      `for_title` int(4) default NULL,
      `appointment_type` int(1) default NULL,
      `company` varchar(600) NOT NULL,
      `meeting` varchar(400) NOT NULL,
      `location` varchar(600) NOT NULL,
      `postcode` varchar(255) default NULL,
      `the_date` date default NULL,
      `estimated_start_time` datetime default NULL,
      `estimated_end_time` datetime default NULL,
      `about` text NOT NULL,
      `feedback` text NOT NULL,
      `live` int(1) NOT NULL COMMENT '1 = Happening, 2= Cancelled',
      PRIMARY KEY  (`schedule_id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1008 ;

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, that looks okay

    do you know how to dump rows? dump a few for me, and make sure the one you mentioned in post #3 is included
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep - sure thanks again. The last one shows 11:45 - 12:45pm:

    Code:
    INSERT INTO `reps_schedule` (`schedule_id`, `user_id`, `for_title`, `appointment_type`, `company`, `meeting`, `location`, `postcode`, `the_date`, `estimated_start_time`, `estimated_end_time`, `about`, `feedback`, `live`) VALUES
    (1020, 12, 51, 1, 'Another Place', 'jack', 'Area', 'PO5', '2011-01-11', '2011-01-11 09:40:00', '2011-01-11 10:15:00', 'details here', '', 1),
    (1021, 12, 51, 1, 'Some Place', '.', 'Area', 'PO5', '2011-01-11', '2011-01-11 10:30:00', '2011-01-11 12:15:00', 'details here', '', 1),
    (1022, 12, 51, 6, 'Place Again', 'julian', 'Area', 'PO5', '2011-01-11', '2011-01-11 12:30:00', '2011-01-11 12:45:00', 'details here', '', 1),
    (1023, 12, 51, 1, 'New Place', 'george', 'Area', 'PO5', '2011-01-11', '2011-01-11 12:55:00', '2011-01-11 13:15:00', 'details here', '', 1),
    (1024, 12, 51, 1, 'Round Road', 'emma', 'Area', 'PO5', '2011-01-11', '2011-01-11 13:40:00', '2011-01-11 14:15:00', 'details here', '', 1),
    (1025, 12, 51, 1, 'Some Place', 'mr Jones', 'Area', 'P02', '2011-01-11', '2011-01-11 14:30:00', '2011-01-11 15:30:00', 'details here', '', 1),
    (1026, 12, 51, 1, 'Fred Jones', 'george', 'Area', 'PO5', '2011-01-11', '2011-01-11 11:45:00', '2011-01-11 12:45:00', 'details here', '', 1);

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, i tried the query from post #3...
    Code:
    SELECT * 
      FROM `reps_schedule` 
     WHERE estimated_start_time <= '2011-01-10 12:30:00' 
       AND estimated_end_time   >= '2011-01-10 13:00:00' 
       AND user_id = 12
    and of course it returned 0 rows on the data you provided, which was all for the next day

    so i tried it with this query instead --
    Code:
    SELECT * 
      FROM `reps_schedule` 
     WHERE estimated_start_time <= '2011-01-11 12:30:00' 
       AND estimated_end_time   >= '2011-01-11 13:00:00' 
       AND user_id = 12
    and again it returned no rows, because none of them fit that particular range

    so then i did
    Code:
    UPDATE reps_schedule
    SET estimated_start_time = estimated_start_time - INTERVAL 3 HOUR
    and ran the query again and it worked fine

    so i guess your problem was that none of the data matched your query requirements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks and sorry to be a pain. I apologise I don't think i've explained this too well. I want to check if half an hour slots are filled for meetings on 2011-01-11:

    So for example:

    11:30:00 - 12:00:00
    12:00:00 - 12:30:00
    12:30:00 - 13:00:00

    So with this row:

    Code:
    (1026, 12, 51, 1, 'Fred Jones', 'george', 'Area', 'PO5', '2011-01-11', '2011-01-11 11:45:00', '2011-01-11 12:45:00', 'details here', '', 1);
    The meeting starts at 11:45:00 and runs till 12:45:00 so each of the above timeslots would return a row, as it falls in each of those slots.

    If that makes sense?

    But I need to create a query for each of those timeslots which allows for this, which is where i'm stuck?

    Thanks

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    finally, we arrive at the real problem

    you're trying to determine if there are overlaps, not just retrieving row where some value is between two values

    have a look at one of thes threads...

    http://www.sitepoint.com/forums/showthread.php?t=664040
    http://www.sitepoint.com/forums/showthread.php?t=631475
    http://www.sitepoint.com/forums/showthread.php?t=607741

    notice a pattern?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I get it so I pretty much reverse it looking for:

    Code:
    SELECT *
    FROM `reps_schedule`
    WHERE estimated_end_time >= '2011-01-11 12:00:00'
    AND estimated_start_time <= '2011-01-11 12:30:00'
    AND user_id =12
    Thanks for helping me (again!)


Bookmarks

Posting Permissions

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