Fetch a data if the time duration between added date and current date is not greater than or equal to 2 hours

Question

I am creating a booking management system. I got stuck in the booking cancellation part. I want to allow users to cancel their orders if their booking time and the current time duration is between 2 hours because I want to restrict the users to cancel their booking if their booking time and current time duration is greater than or equal to 2 hours.

I want to generate a query that returns all the bookings whose booking time is less than 2 hours. How can I achieve this?

This is my database structure.

please check https://stackoverflow.com/questions/29446361/select-rows-having-time-difference-less-than-2-hour-of-a-single-column — Vishvadeep singh, Aug 05 '19 at 05:27

score 0 · Answer 1 · 2019-08-05T06:35:45.913

0

You can extract the hour part of your date. Refer to this link

Then using this query to get those less than 2 hours.

SELECT * FROM table1 WHERE tdate - 120 < EXTRACT(MINUTE FROM now())

edited Aug 05 '19 at 06:35

answered Aug 05 '19 at 05:37

there is no such thing as `datepart` in MySQL the same post says https://stackoverflow.com/a/13402434/10342514 – James Aug 05 '19 at 05:43
@donic It did not fix. – Alisha Lamichhane Aug 05 '19 at 06:21
@donic Thanks, but even this does not return any row. I have two data that are added at 1.00pm and 1.14 pm. – Alisha Lamichhane Aug 05 '19 at 08:23

score 0 · Answer 2 · edited Aug 05 '19 at 05:54

0

SELECT * FROM `TableName` where TIMEDIFF(NOW(),Your_date_ColumnName) < '02:00:00.000000'

edited Aug 05 '19 at 05:54

Fahmi

37,315
5
22
31

answered Aug 05 '19 at 05:49

hitaxi

27
5

This return all the rows that are booked today. – Alisha Lamichhane Aug 05 '19 at 06:25
this will return all the rows that are booked and whose booking time is less than 2 hours. – hitaxi Aug 05 '19 at 11:23
SELECT * FROM `TableName` where TIMEDIFF(NOW(),added_date) < '02:00:00.000000' – hitaxi Aug 05 '19 at 11:30

score 0 · Answer 3 · answered Aug 05 '19 at 06:02

0

Assuming that booking_time is in MySQL standard format.

Try this and the below query will use index if you have one in booking_time column

SELECT * 
FROM booking_table 
WHERE booking_time BETWEEN CURRENT_TIMESTAMP() - INTERVAL 2 HOUR AND CURRENT_TIMESTAMP()

answered Aug 05 '19 at 06:02

James

1,819
2
8
21

I am generating this query `SELECT * FROM booking WHERE added_date BETWEEN CURRENT_TIMESTAMP() - INTERVAL 2 HOUR AND CURRENT_TIMESTAMP() AND sender_id = "4" ORDER BY booking.id DESC` but it soes not return any row. – Alisha Lamichhane Aug 05 '19 at 06:26
Does `sender_id =4` is in the 2 hours time? Just remove `AND sender_id="4" ORDER BY booking.id DESC` then check what sender_id are really present – James Aug 05 '19 at 06:28
It does not return any row even after removing sender_id. And,the sender_id is int data, that is the user id. – Alisha Lamichhane Aug 05 '19 at 06:31
Then you don't have any booking who created 2 hour before? – James Aug 05 '19 at 06:34
I don't want bookings of two hours before. I want bookings that haven't crossed two hours from the current time. Suppose, if it is 2.00 pm, I want data that are booked between 12.00pm - 2.00pm. – Alisha Lamichhane Aug 05 '19 at 07:52
That's what i have shared you. `CURRENT_TIMESTAMP()` which gives you 2.00 pm `CURRENT_TIMESTAMP() - INTERVAL 2 HOUR` which gives 12.00 pm so on comparison it will show the data which is between those time for `added_date`. If its not showing share the table struc and sample data – James Aug 05 '19 at 08:02
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/197479/discussion-between-alisha-lamichhane-and-james). – Alisha Lamichhane Aug 05 '19 at 08:06
@AlishaLamichhane I hope your timezone is different in your application rather than in mysql – James Aug 05 '19 at 11:05

Fetch a data if the time duration between added date and current date is not greater than or equal to 2 hours

3 Answers3