Retrieving date range data from a database

Question

I always save my dates as a unix timestamp in the database. Now I have two dates: the start date and the end date of some images that I would like to display on the website. The image should be visible in the "Start Date" and disappear after reaching the "End Date".

I am using this simple query at the 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 each time returns an empty result, 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 portion of 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 benefit that you can really use any indexes that you might have at the start and end dates.

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

Answer 3

The problem is that you are comparing as string types, not as a timestamp. Therefore, the format matters. Switching to yyyy-mm-dd makes it work (because the most significant part, the year, is on the left).

But much better, and optimized with indexes to compare like timestamps.

 WHERE start_date <= unix_timestamp(curdate()) AND end_date >= unit_timestamp(curdate()) 

or, for some SQL engines, use the BETWEEN statement:

 WHERE unix_timestamp(curdate()) BETWEEN start_date AND end_date