badbye badbye - 4 months ago 8
MySQL Question

Mysql: How to retrieve datetime which created within 9hour59mins30s from now?

Originally I have MySQL query as below:

SELECT sub.id, sub.content, sub.time, date_format(sub.time, '%d %b %Y')
FROM (SELECT * from accounts) AS sub
ORDER BY time ASC LIMIT 3


Now I have to add another condition, that is, the retrived account must be created within 9hour59mins30s from now.

So I tweaked with many tries but failed, such as:

SELECT sub.id, sub.content, sub.time(curdate(), interval 9 hour 59 min 30 sec),
date_format(sub.time, '%d %b %Y')
FROM (SELECT * from accounts) AS sub
ORDER BY time ASC LIMIT 3


How could I add the new condition into my original SQL?

Answer

I'm not sure that I fully understand the logic. The following gets all accounts created in the past 9 hours 30 minutes and 30 seconds:

select sub.id, sub.content, sub.time, date_format(sub.time, '%d %b %Y')
from accounts sub
where sub.time >= date_sub(now(), interval 30 + 59*60 + 9*60*60 second)
order by time ASC
limit 3;

In most of the world, 10 hour would be a fine approximation.

Comments