mySQL SELECT timestamp(now()-3000);

Question

I'm trying to make a query which brings back results based on a timestamp, say an interval of 30 minutes.

So what I figured out is that I can

SELECT * FROM x WHERE ts BETWEEN timestamp(now()-3000) AND timestamp(now())

So this will query everything from x with timestamps in column ts within the last 30 minutes.

However, this only works after now() is past the yyyy-mm-dd HH:30:00 mark because anytime before it will result in NULL... this is rather cumbersome and I don't understand why it won't just subtract the friggin minutes from the hour!

Please help me out! I couldn't find any other method of doing a query within the last 30 minutes, that is what I'm trying to achieve.

Best regards,

John

Answer 1

SELECT * FROM x WHERE ts BETWEEN timestamp(DATE_SUB(NOW(), INTERVAL 30 MINUTE)) AND timestamp(NOW())

Answer 2

SELECT * FROM x WHERE ts BETWEEN NOW() - INTERVAL 30 MINUTE AND NOW();