MySQL – How to Get Closest Previous Date

datedatetimeMySQL

I need to get the posts closest to a date.

SELECT id
     , datetime
  FROM dates
 WHERE id != 150003
   AND datetime > FROM_UNIXTIME(1549622615)
 ORDER BY datetime
 LIMIT 1

Returns a correct result:

{"id":"150015","datetime":"2019-04-13 17:47:55"}]

but when I change to "datetime < FROM_UNIXTIME(1549622615)" I get the result:

{"id":"1","datetime":"1970-01-10 12:06:07"}

when there are many posts closer to the datetime.

Any insights why?

Best Answer

When you change it to datetime < FROM_UNIXTIME(1549622615) you also need to change the sort order to ORDER BY datetime DESC to get the closest date.

Related Solutions

SQL – How to Get Max Date for Each Year from List of Dates

It is possible that I am missing something, but you should be able to get the result easily using the max() aggregate and a GROUP BY:

select
  max(date) maxdate,
  year
from yourtable
group by year;

See SQL Fiddle with Demo

Mysql – Find the closest bar, when searching for a drink

set @x = 15;
set @y = 25;

select sqrt(pow(b.lat-@y,2) + pow(b.lng-@x,2)) as distance,  b.name, d.name
from `drinks` d
 join `drink__bars` db on d.id = db.drink_id
 join  `bars` b on b.id = db.bar_id
where d.`name` = 'Beer'
order by distance asc;

Full sqlfiddle, with some data. Use your preferred distance function to replace the plain one I used.

I would recommend against having Id on many-to-many relations. And prefer NOT NULL always, unless you have a very strong case to allow NULLs (you don't).

Best Answer

Related Solutions

SQL – How to Get Max Date for Each Year from List of Dates

Mysql – Find the closest bar, when searching for a drink

Related Question