1

I have a cron that runs a script in my rails app hourly looking for new transactions to run. Typically this only picks up new transactions set to start on the current day, until midnight UTC, when all the following day's transactions become due. My rails app is operating in Central Time, but Postgres is set to UTC. Since I'm using a scope with a where clause, and now() to compare the dates, transactions for the new day run in the early evening (in the US). Is there a downside to switching Postgres's timezone to Central Time? From what I can tell, it will record all timestamps in UTC regardless, but will run queries based on its timezone setting.

shanebonham
  • 2,219
  • 2
  • 18
  • 19
  • 1
    PostgreSQL isn't do it, Rails is doing it. PostgreSQL has `timestamp` and `timestamp with time zone` types but Rails wants to use the `timestamp` type and make everything (implicitly) UTC inside the database. Various ActiveRecord Ruby-to-PostgreSQL type conversions will include the time zone adjustment behind your back, this can cause problems if some logic is in the SQL (such as `now()`) and some is in Rails. – mu is too short Jan 07 '13 at 20:53
  • [This closely related answer may be of help.](http://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql/9576170#9576170) – Erwin Brandstetter Jan 07 '13 at 21:10

1 Answers1

1

You should always use UTC inside the application, and only perform conversions to other timezones at the edge of the application where necessary - such as in controllers or views.

In your query, perform some date manipulation on now() to translate it to the timezone you need.

yfeldblum
  • 65,165
  • 12
  • 129
  • 169