MySQL在给定间隔之间选择行

Question

table videos

id | title  | time
------------------------
1  | o'najr | 1406332800

I want to select rows which are between the interval of 6 months from a given time. Both newer or older

So if the query time is for ex. 1446076800 all the rows which 6 months older or 6 months newer than this will be selected.

time is in the UNIX TIMESTAMP format.

So if a video is uploaded on October 2015 all the videos which are uploaded on April 2015 and newer will be selected. Also all the videos from October 2015 up to April 2016 will be selected.

6 months <---- || query time || ----> 6 months

This is my query but it doesn't work at all. It just as a structure not a working query.

:time is the variable which will be used for the query.

select `title` 
from `videos` 
where DATE_FORMAT(FROM_UNIXTIME(time - :time)) > INTERVAL 6 MONTH 
      or 
      DATE_FORMAT(FROM_UNIXTIME(:time - time)) < INTERVAL 6 MONTH

Answer 1

Your query doesn't quite make sense. You're trying to compare a specific date to a time interval.

Try this:

SELECT title FROM videos 
WHERE FROM_UNIXTIME(time) BETWEEN
    DATE_SUB(FROM_UNIXTIME(:time), INTERVAL 6 MONTH) 
    AND DATE_ADD(FROM_UNIXTIME(:time), INTERVAL 6 MONTH)