Help with a MySQL Unix timestamp Query

Question

I am trying to create a custom MySQL for use with the Expression Engine CMS. The purpose of the query is to display events that are happening today or in the future.

The problem is that the EE field type that allows you to put in the date and converts it into a unix timestamp. If I pick the 26th July it puts in the date value "25th July 23:00".

As you see from my query below it almost works but I need to add 24 hours onto the values that are used in the conditional part of the statement. I want events that occur on the day "for example today 25th July" to be displayed up until 23:00 hours that day then be removed. I almost have it I am just stuck on how to add 24 hours to the conditional.

           SELECT t.entry_id, 
                       t.title, 
                       t.url_title, 
                       d.field_id_13 AS event_lineup, 
                       d.field_id_14 AS event_details, 
                       d.field_id_15 AS event_day, 
                       d.field_id_16 AS event_flyer_front, 
                       d.field_id_17 AS event_flyer_back, 
                       d.field_id_18 AS event_facebook, 
                       d.field_id_12 AS event_date 
             FROM `exp_weblog_titles` AS t 
NATURAL JOIN `exp_weblog_data` AS d 
           WHERE d.weblog_id = 5 
               AND CAST(d.field_id_12 AS UNSIGNED) >= (unix_timestamp(Now())) 
      ORDER BY d.field_id_12 ASC

Answer 1

What I think might be happening is your timestamps get adjusted for the time zone, and that adjustment is configured differently in the CMS and on the server.