Search code examples
mysqlruby-on-railstimeutc

Rails 4 Ignoring UTC conversion to MySQL database


I have a Rails 4 application that according to best practices is storing dates in my MySQL database in UTC. This is ok.

However, I have an scenario that is not working properly.

I have an event manager which is always running looking for new events (stored in database) to run. These events are stored in database according to user actions on the site. The events table in DB has an execution_date field that stores the event planned execution date. According to this, when a user does something that creates an event event execution_date is converted to UTC.

In Rails, I use Time.now --> 2014-10-04T01:02:10-07:00 (I´m 7 hours from UTC). In DB this time is converted to UTC, so I see 2014-10-04 08:02:10. I think this is right too.

But the problem is the event manager is running next query to get events pending of execution:

SELECT e.*
                               FROM events e, event_states es
                               WHERE e.execution_date <= NOW()
                               AND es.name = 'Pending'
                               AND es.id = e.event_state_id

When I run this query, NOW() returns my local time which is 2014-10-04 01:02:25. So, the event that should be executed now, will be executed in 7 hours.

So, as summary, execution_date should always be stored in server local time.


Solution

  • According to @mccannf comment above, I didn´t update my DB to UTC time, but I replaced my query with

    SELECT e.*
                                   FROM events e, event_states es
                                   WHERE e.execution_date <= UTC_TIMESTAMP()
                                   AND es.name = 'Pending'
                                   AND es.id = e.event_state_id
    

    NOTE: Using UTC_TIMESTAMP() gets the UTC time.