SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    i have booking table, in that two fields are ther

    Hi

    All

    I have a booking table in mysql, which consists of fields like booking required date and booking time.

    I want to fetch the records from booking table when booking time is lessthan 2 hours from the current system time

    Please help me out


    Thanks

    MD.Samiuddin

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT ...
      FROM ...
     WHERE booking_time 
           BETWEEN CURRENT_TIMESTAMP 
               AND CURRENT_TIMESTAMP + INTERVAL 2 HOUR
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Thanks for you response.

    I want to fetch the records based on current date and time less than 2 hours from system time


    please help me out



    Thanks

    MD.Samiuddin

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by samiuddin.adse View Post
    I want to fetch the records based on current date and time less than 2 hours from system time
    okay, two questions

    1. what's the difference between "current date and time" and "system time"?

    2. what was wrong with the solution i already gave you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi

    I am sorry, I am new to programming i am bit confused
    I have a booking table which consists of following fields.
    Booking id
    custname
    address
    booking date
    booking time

    suppose a customer enter the booking date as 5-jan-2011 and time as 16:00 hrs
    and another customer enter the booking date as 6-jan-2011 and time as 16:00hrs
    i want to display the records to dispatcher screen just 2 hrs before the booking
    thanks & Regards
    MD.Samiuddin

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    is this your table? can you change it?

    if so, you should have only one column, booking_datetime, instead of separate date and time columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't have rights to change the table structure

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by samiuddin.adse View Post
    I don't have rights to change the table structure
    that's too bad, because it makes all your sql much more complex
    Code:
     WHERE booking_date + 
              INTERVAL TIME_TO_SEC(booking_time) SECOND 
           BETWEEN CURRENT_TIMESTAMP 
               AND CURRENT_TIMESTAMP + INTERVAL 2 HOUR
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Thanks for your reply. I tried the query but it is not giving the result



    Please help me out




    Thanks

    MD.Samiuddin

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by samiuddin.adse View Post
    I tried the query but it is not giving the result
    well, i tried the query and it works just fine
    Code:
    CREATE TABLE temp2
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , bdate DATE
    , btime TIME
    );
    INSERT INTO temp2 (bdate,btime) VALUES
      ('2011-01-05', '01:37:00')
    , ('2011-01-05', '03:37:00')
    , ('2011-01-05', '05:37:00')
    , ('2011-01-05', '07:37:00')
    , ('2011-01-05', '09:37:00')
    , ('2011-01-05', '11:37:00')
    , ('2011-01-05', '13:37:00')
    , ('2011-01-05', '15:37:00')
    , ('2011-01-05', '17:37:00')
    , ('2011-01-05', '19:37:00')
    , ('2011-01-05', '21:37:00')
    , ('2011-01-05', '23:37:00')
    , ('2011-01-06', '01:37:00')
    , ('2011-01-06', '03:37:00')
    , ('2011-01-06', '05:37:00')
    , ('2011-01-06', '07:37:00')
    , ('2011-01-06', '09:37:00')
    , ('2011-01-06', '11:37:00')
    , ('2011-01-06', '13:37:00')
    , ('2011-01-06', '15:37:00')
    , ('2011-01-06', '17:37:00')
    , ('2011-01-06', '19:37:00')
    , ('2011-01-06', '21:37:00')
    , ('2011-01-06', '23:37:00')
    ;
    SELECT * FROM temp2
    WHERE bdate + 
              INTERVAL TIME_TO_SEC(btime) SECOND 
           BETWEEN CURRENT_TIMESTAMP 
               AND CURRENT_TIMESTAMP + INTERVAL 2 HOUR;
    works just fine, right?

    so you must be doing something wrong

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

  11. #11
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for your reply


    I will try out once again


    MD.Samiuddin


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
  •