Getting data in a date range from database

Question

I always store my dates as a unix timestamp in the database. Now I have two dates: Start date and End date for some images I would like to show on the website. The image should be visible on the "Start date" and disappear once the "End date" is reached.

I use this simple query at this moment:

SELECT name, image, link
FROM exclusive
WHERE CURDATE( ) >= from_unixtime( start_date, '%d-%m-%Y' )  
AND CURDATE( ) <= from_unixtime( end_date, '%d-%m-%Y' )
AND category = 'movies'
LIMIT 0 , 5

But this is returning an empty result every time, although there are two fields within the data range.

What am I doing wrong here?

Thanks in advance.

Answer 1

Instead of formatting, use the DATE() function to remove the time part of your timestamps:

SELECT name, image, link
FROM exclusive
WHERE 
    CURDATE( ) BETWEEN DATE(from_unixtime( start_date ) )
    AND DATE(from_unixtime( end_date ) )
    AND category = 'movies'
LIMIT 0 , 5

Answer 2

Try this, it has the added advantage that you'll actually be able to use any indexes you might have on start and end date.

SELECT name, image, link
FROM exclusive
WHERE UNIX_TIMESTAMP() BETWEEN start_date AND end_date
AND category = 'movies'
LIMIT 0 , 5