MySQL Date Interval not working

Question

I'm having troubles selecting future sessions were the date is 21 days from now. So not between now and 21 days but ONLY sessions that will take place 21 days from this day.

In my table dx_sessions_dates I have a field timestart of type BIGINT where a timestamp is saved (why BIGINT and not TIMESTAMP? -> Not my DB, but can't change it ... ).

My SQL Query is :

SELECT timestart, timefinish, sessionid FROM `dx_sessions_dates` WHERE timestart = UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL 21 DAY))

As you can see I want to select all the sessions where timestart is 21 days from now. 21 days from now should be 15 april 2015.

The query always returns 0 rows ... . While in my table I have a timestart with value = 1429081200 . And when you calculate the date with this you see it's 15 april 2015. Why don't I get any rows back?

Answer 1

The unixtimestamp you calculate is never exactly the moment of the value you stored. You just need the same day.

Try

SELECT timestart, timefinish, sessionid 
FROM `dx_sessions_dates` 
WHERE date(FROM_UNIXTIME(timestart)) = curdate() + interval 21 DAY

Answer 2

Try using BETWEEN to find any rows that have timestart values anywhere within the 24-hour period that is 21 days from today:

SELECT timestart, timefinish, sessionid 
FROM dx_sessions_dates
WHERE timestart BETWEEN UNIX_TIMESTAMP(adddate( curdate(), 21)) AND UNIX_TIMESTAMP(adddate(curdate(), 22))

curdate() returns midnight at the start of today, so 21 days from today is up to 22 days from midnight.