I have booking table, in that two fields are ther

samiuddin_adse · January 4, 2011, 10:08am

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

r937 · January 4, 2011, 10:46am

SELECT ...
  FROM ...
 WHERE booking_time 
       BETWEEN CURRENT_TIMESTAMP 
           AND CURRENT_TIMESTAMP + INTERVAL 2 HOUR

samiuddin_adse · January 4, 2011, 11:37am

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

r937 · January 4, 2011, 2:16pm

okay, two questions

what’s the difference between “current date and time” and “system time”?
what was wrong with the solution i already gave you?

samiuddin_adse · January 4, 2011, 2:54pm

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

r937 · January 4, 2011, 2:56pm

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

samiuddin_adse · January 4, 2011, 3:05pm

I don’t have rights to change the table structure

r937 · January 4, 2011, 4:01pm

that’s too bad, because it makes all your sql much more complex

 WHERE booking_date + 
          INTERVAL TIME_TO_SEC(booking_time) SECOND 
       BETWEEN CURRENT_TIMESTAMP 
           AND CURRENT_TIMESTAMP + INTERVAL 2 HOUR

samiuddin_adse · January 5, 2011, 2:17pm

Hi

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

Please help me out

Thanks

MD.Samiuddin

r937 · January 5, 2011, 2:46pm

well, i tried the query and it works just fine

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

samiuddin_adse · January 5, 2011, 3:23pm

thanks for your reply

I will try out once again

MD.Samiuddin