Here's a blog article I just wrote which may be relevant to the Sinatra and Heroku communities. I've pasted the HTML into this email for your perusal, or you can read it and check other comments at
http://pivotallabs.com/users/alex/blog/articles/1142-utc-vs-ruby-activerecord-sinatra-heroku-and-postgres --- Alex Chaffee - [email protected] - http://alexch.github.com Stalk me: http://friendfeed.com/alexch | http://twitter.com/alexch | http://alexch.tumblr.com Now that I'm starting to use DelayedJob to perform jobs in the future in my Heroku Sinatra app, its important that they happen at the scheduled time. But unless you pay attention, you'll find that times get mysteriously changed -- in my case, since I'm in San Francisco in the wintertime, by +/-8 hours -- which means that some conversion to or from UTC is being attempted, but it's only working halfway. Trying to keep a handle on which libraries are attempting, and which are failing, to convert times is a losing battle, so I'm trying to do the right thing and save all my times in the database in UTC, and convert them to and from the user's local time as close to the UI as possible. Unfortunately, a variety of gotchas in Ruby and ActiveRecord and PostgreSQL makes this trickier than it should be. Here's a little catalog of my workarounds. ------------------------------ You must set both Time.zone = "UTC" and ActiveRecord::Base.default_timezone = :utc. Since I'm using Sinatra, not Rails, this stuff goes either in main (i.e. not inside any class) right after require 'active_record', or in a configure block in your app, depending on your preference. ------------------------------ When ActiveRecord creates queries -- which are used for both reading *and* writing, mind you -- it will only convert to UTC times that are instances of ActiveSupport's proprietaryTimeWithZone class. It will *not* convert regular Ruby Time objects, even though Time objects are perfectly aware of their time zones, and AR is perfectly aware that you'd prefer they be written as UTC (due to the default_timezone setting). This is clearly a bug IMHO, but the Rails core marked the bug as "will not fix", so w/e. Here's a monkey patch, courtesy of Peter Marklund <http://marklunds.com/articles/one/402>: module ActiveRecord module ConnectionAdapters # :nodoc: module Quoting # Convert dates and times to UTC so that the following two will be equivalent: # Event.all(:conditions => ["start_time > ?", Time.zone.now]) # Event.all(:conditions => ["start_time > ?", Time.now]) def quoted_date(value) value.respond_to?(:utc) ? value.utc.to_s(:db) : value.to_s(:db) end end end end ------------------------------ When outputting timestamps to a UI -- either inside HTML or in a JSON API -- you'll probably want to use Time#strftime. Beware: on Mac OS X under Ruby 1.8, the %z (lowercase Z) selector will emit the *local time zone*, not the zone of the Time object you've called strftime on. The solution is to either use %Z (capital Z) or just a plain Z which stands for Zulu Time. The latter is OK if you know you're using UTC, which, if you've followed my advice, you probably do. This is a pretty annoying issue, since it's much safer to use %z's hour offsets than %Z's three-letter codes, since the three-letter codes can be ambiguous, and in any case require an extra conversion to time offset, so you may as well just emit the offset. Here are some methods on Time you may want to use that work around this %z issue: # Note: do NOT call this file 'time.rb' :-D require 'time' class Time def full_date_and_time strftime('%Y-%m-%d %H:%M:%S %Z') end def iso8601 strftime('%Y-%m-%dT%H:%M:%SZ') # the final "Z" means "Zulu time" which is ok since we're now doing all times in UTC end end That iso8601 method comes in really handy when you're using the excellent timeago jQuery plugin <http://github.com/rmm5t/jquery-timeago> by Ryan McGeary (@rmm5t). ------------------------------ By default PostgreSQL saves timestamps *sans* time zone, which means that ActiveRecord interprets them as being in the default_timezone. If you want to be extra clear and save them*with* time zone, you'll have to change the Postgres adapter's type mapping. ActiveRecord doesn't let you configure this but here's a monkey patch, courtesy of Chirag Patel<http://jacqueschirag.wordpress.com/2007/08/13/timestamp-support-rails-and-mysqlpostgresqloracle/> (with a couple of mods): require 'active_record/connection_adapters/postgresql_adapter' class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter < ActiveRecord::ConnectionAdapters::AbstractAdapter def native_database_types { :primary_key => "serial primary key".freeze, :string => { :name => "character varying", :limit => 255 }, :text => { :name => "text" }, :integer => { :name => "integer" }, :float => { :name => "float" }, :decimal => { :name => "decimal" }, :datetime => { :name => "timestamp with time zone" }, :timestamp => { :name => "timestamp with time zone" }, :time => { :name => "time" }, :date => { :name => "date" }, :binary => { :name => "bytea" }, :boolean => { :name => "boolean" } } end end It turned out that I didn't need this, so I ended up commenting it out. It may be that storing timestamps *with* time zones will cause a hiccup with some other random DB code, so watch out. If you do use it, and you've already got some data, make sure to write a migration that changes the types of all extant datetime and timestamp fields. ------------------------------ That's all I've got for right now. I'm sure some more problems will come up on March 14, 2010 <http://eclipse.gsfc.nasa.gov/SEhelp/daylightsaving.html> ... Read more: http://pivotallabs.com/users/alex/blog/articles/1142-utc-vs-ruby-activerecord-sinatra-heroku-and-postgres#ixzz0dNsLwBE9 -- You received this message because you are subscribed to the Google Groups "Heroku" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/heroku?hl=en.
