PHP and mysql getting the nearest time of today

I'm storing a date column using time() (php function). My date column has a int data type since i'm using time() php function.

This is a sample:

date
1317957366
1318043891

My question is, how would I query or create a logic that would get a result who has a time that is nearest on today's time?

I want to query it via mysql not on the php 开发者_高级运维logic, but if you have suggestion that I should do it on php, then that would also be fine.

Thanks and your help would be greatly appreciated and rewarded. :)

---

Compare the absolute value of the current time - the stored time, and select the MIN() of those:

Perhaps something like this?

SELECT *
FROM tbl
GROUP BY `date`
HAVING MIN(ABS(UNIX_TIMESTAMP(NOW()) - `date`));

If that doesn't work properly, try with a subquery:

SELECT *
FROM tbl
WHERE  ABS(UNIX_TIMESTAMP(NOW()) - `date`) IN
  SELECT (MIN(ABS(UNIX_TIMESTAMP(NOW()) - `date`)) FROM tbl);

Since you have stored the dates as INT instead of as MySQL DATETIME, you need to convert the current timestamp NOW() to a Unix timestamp, then subtract the stored date.

---

You should convert your date/time to UNIX timestamp and store it in datetime type in mysql. Why? So you can use the various functions that are part of mysql such as year(), month() etc.

http://www.richardlord.net/blog/dates-in-php-and-mysql

---

Can't you just

SELECT * FROM `tbl` ORDER BY ABS(UNIX_TIMESTAMP(NOW()) - `date`) LIMIT 0,1

?

You may also try to find row with minimum distance from now with mysql variables:

SET @min = 1000000000000, @minid = 0;

SELECT 
  @minid := IF(@min > ABS(UNIX_TIMESTAMP(NOW()) - `date`), id, @minid), 
  @min := IF(@min > ABS(UNIX_TIMESTAMP(NOW()) - `date`), ABS(UNIX_TIMESTAMP(NOW()) - `date`), @min) 
FROM 
  `tbl` ;

SELECT * FROM `tbl` WHERE id = @minid;

You might also calculate value of UNIX_TIMESTAMP(NOW()) in PHP (time() gives it) and use it in your queries.