SitePoint Sponsor

User Tag List

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

    Querying database for dates and times help?

    Hi,

    I want to query times in my database, but i'm stuck. So for example I have details of a meeting which:

    Starts: 2010-12-21 09:00:00
    Ends: 2010-12-21 10:00:00

    Saved into my database.

    So what I want to do is run a query which checks a SET half an hour time period which is between ' 2010-12-21 09:00:00' and ' 2010-12-21 09:30:00' which the above period falls into, but i'm pretty stuck as the query i'm working on below won't work as the ending period is at 10am, even though 9.30 is between 9 and 10​?:

    Code:
    SELECT * FROM `TBL` WHERE start_time >= '2010-12-21 09:00:00' AND ends <= '2010-12-21 09:30:00' AND user_id = 3
    Anyone kindly help?

    Thanks

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    WHERE start_time < '2010-12-21 09:30:00'
    AND ends > '2010-12-21 09:00:00'

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, but that won't work as it would get all entries before that timeframe in my database and all entries after.

    What I need is check a time period '2010-12-21 09:30:00'

    and output al records that cover this period, so say I have the columns as:

    Start: 2010-12-21 09:00:00
    End: 2010-12-21 09:29:00
    would not output any data

    Start: 2010-12-21 09:00:00
    End: 2010-12-21 19:29:00
    would output rows

    Hope that makes sense.

    Thanks

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You should decide what you want: a 30 minute period or a single time.
    But for the solution it makes little difference. Did you try my where conditions? Or did you just decide that by the looks of it it won't work?


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
  •