CODEWITHSUNDEEP
phpmysql
user1929236
user1929236

Reputation: 125

Retrive the data from my-sql database within last 30 minutes

I am using the database table with the following details:

Column Number | Column name | Data type
---------------------------------------------
1             | cardid      | int(5)
2             | price       | decimal(5,2)
3             | isbuyprice  | int(1)
4             | date        | date
5             | time        | time

Now, i want to retrieve the data which is inserted into the table in the last 30 minutes. I tried using the following code but it retrieved me all the rows of my table:

"SELECT * FROM table1 WHERE time > (now() - INTERVAL 30 MINUTE)";

Upvotes: 0

Views: 1147

Answers (5)

fthiella
fthiella

Reputation: 49049

You have two separated fields, one for the date and one for the time, so you could use something like this:

SELECT *
FROM table1
WHERE
  UNIX_TIMESTAMP(date) + TIME_TO_SEC(time) >=
  UNIX_TIMESTAMP(NOW() - INTERVAL 30 MINUTE)

UNIX_TIMESTAMP will convert date to seconds since 1970-01-01, we add the seconds of the time part with time_to_sec, and we compare the result with NOW()-INTERVAL 30 MINUTE, converted to seconds.

Upvotes: 1

rohitarora
rohitarora

Reputation: 1365

SELECT * FROM table1 WHERE time BETWEEN CURTIME() AND  TIME(DATE_SUB(NOW(), INTERVAL 30 MINUTE))

hope this can help you

Upvotes: 1

Redian
Redian

Reputation: 334

Select * from table1 where concat (date, ' ', time) > ( now() - interval 30 minute)

Upvotes: 0

Ravindra Gullapalli
Ravindra Gullapalli

Reputation: 9158

Check this

SELECT * FROM table1 
WHERE date=CURDATE() AND TIME_TO_SEC(time) > TIME_TO_SEC(CURRENT_TIME()) - 1800

Upvotes: 0

Ripa Saha
Ripa Saha

Reputation: 2540

"SELECT * FROM table1 WHERE time > (time(now()) - INTERVAL 30 MINUTE) 
 and date=CURDATE()";

the best solution will be if you able to change your db table then change date field to timestamp and remove time field. then the above query will work. otherwise use the below code with your current db structure.

"SELECT * FROM table1 WHERE time > time(time(now())-interval 30 minute) 
 and date=CURDATE()";

think it will work. thanks.

Upvotes: 1

Related Questions