mysql query to retrieve only records with a start_date > now()

Question

I am trying to retrieve all records where the start_date is greater than the current timestamp. Any ideas why this might not be working:

WHERE (UNIX_TIMESTAMP(event_dates.start_date)) > DATE(NOW())

and this is the example start_date column 2010-10-30

Answer 1

• UNIX_TIMESTAMP returns an integer with second since the epoch
• DATE() returns a string like 'yyyy-mm-dd'

Comparing those 2 lead to unexpected results I'm sure. Either use UNIX_TIMESTAMP or DATE, but do it for both. (or do a UNIX_TIMESTAMP(DATE(NOW())) so you're comparing the proper formats.

Answer 2

You are comparing a date value (yyyy-mm-dd) with the UNIX epoch, so your query does not work.

Just make sure you are using the right data types for the appropriate case. You need dates, use a datetime or date, not timestamp. Plus the fact that a timestamp column in a MySQL table has special properties.

Also, use an index in this date column. Your query will not be optimized because of the now() function in the where clause. Try to get the value of now in a variable first and run your query using this variable, then the MySQL optimizer will have a chance to optimize your query.