The current Rails practice with PostgreSQL is to use timestamp without zone and use ActiveRecord''s time zone handling. This works fine for Rails but makes it slightly inconvenient for ad hoc, local time queries outside of Rails. I believe there is a way to get the best of both worlds. If when creating the connection, ActiveRecord set the connection time zone to UTC then Rails could handle time zones its way and other clients could use PostgreSQL''s time zone handling. Is there any interest in a patch to this effect? Jack -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
That sounds reasonable -- ActiveRecord could set the connection timezone to UTC when AR::Base.default_timezone is :utc, and not set it to anything (i.e., assume the system local timezone is correct) when :local. Solutions have been offered before for accomodating timestamp with timezone, but they have involved adding another timezone configuration and/or Rails TimeZone -> DB time zone mapping logic to the framework. This is much cleaner. Assuming this doesn''t take much code, and doesn''t negatively impact performance, I''m +1 on the idea. On Mon, Jan 18, 2010 at 6:18 PM, Jack Christensen <jack@jncsoftware.com>wrote:> The current Rails practice with PostgreSQL is to use timestamp without > zone and use ActiveRecord''s time zone handling. This works fine for > Rails but makes it slightly inconvenient for ad hoc, local time queries > outside of Rails. I believe there is a way to get the best of both > worlds. If when creating the connection, ActiveRecord set the connection > time zone to UTC then Rails could handle time zones its way and other > clients could use PostgreSQL''s time zone handling. > > Is there any interest in a patch to this effect? > > > Jack > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Core" group. > To post to this group, send email to rubyonrails-core@googlegroups.com. > To unsubscribe from this group, send email to > rubyonrails-core+unsubscribe@googlegroups.com<rubyonrails-core%2Bunsubscribe@googlegroups.com> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-core?hl=en. > > > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
> Assuming this doesn''t take much code, and doesn''t negatively impact > performance, I''m +1 on the idea.Seems reasonable to me too. Please do try this out.> On Mon, Jan 18, 2010 at 6:18 PM, Jack Christensen <jack@jncsoftware.com> > wrote: >> >> The current Rails practice with PostgreSQL is to use timestamp without >> zone and use ActiveRecord''s time zone handling. This works fine for >> Rails but makes it slightly inconvenient for ad hoc, local time queries >> outside of Rails. I believe there is a way to get the best of both >> worlds. If when creating the connection, ActiveRecord set the connection >> time zone to UTC then Rails could handle time zones its way and other >> clients could use PostgreSQL''s time zone handling. >> >> Is there any interest in a patch to this effect? >> >> >> Jack >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Ruby on Rails: Core" group. >> To post to this group, send email to rubyonrails-core@googlegroups.com. >> To unsubscribe from this group, send email to >> rubyonrails-core+unsubscribe@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/rubyonrails-core?hl=en. >> >> >> > > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Core" group. > To post to this group, send email to rubyonrails-core@googlegroups.com. > To unsubscribe from this group, send email to > rubyonrails-core+unsubscribe@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/rubyonrails-core?hl=en. > >-- Cheers Koz -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
2010/1/20 Geoff Buesing <gbuesing@gmail.com>:> That sounds reasonable -- ActiveRecord could set the connection timezone to > UTC when AR::Base.default_timezone is :utc, and not set it to anything > (i.e., assume the system local timezone is correct) when :local.What would this mean for what is actually stored in the database? Colin> Solutions have been offered before for accomodating timestamp with timezone, > but they have involved adding another timezone configuration and/or Rails > TimeZone -> DB time zone mapping logic to the framework. This is much > cleaner. > Assuming this doesn''t take much code, and doesn''t negatively impact > performance, I''m +1 on the idea. > On Mon, Jan 18, 2010 at 6:18 PM, Jack Christensen <jack@jncsoftware.com> > wrote: >> >> The current Rails practice with PostgreSQL is to use timestamp without >> zone and use ActiveRecord''s time zone handling. This works fine for >> Rails but makes it slightly inconvenient for ad hoc, local time queries >> outside of Rails. I believe there is a way to get the best of both >> worlds. If when creating the connection, ActiveRecord set the connection >> time zone to UTC then Rails could handle time zones its way and other >> clients could use PostgreSQL''s time zone handling. >> >> Is there any interest in a patch to this effect? >> >> >> Jack >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Ruby on Rails: Core" group. >> To post to this group, send email to rubyonrails-core@googlegroups.com. >> To unsubscribe from this group, send email to >> rubyonrails-core+unsubscribe@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/rubyonrails-core?hl=en. >> >> >> > > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Core" group. > To post to this group, send email to rubyonrails-core@googlegroups.com. > To unsubscribe from this group, send email to > rubyonrails-core+unsubscribe@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/rubyonrails-core?hl=en. > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
Times would be stored exactly the same in the database for "timestamp without timezone" columns, which are indifferent to the timezone setting. (Fyi "timestamp without timezone" is the column type created by AR migrations via the #datetime and #timestamps methods.) For "timestamp WITH timezone" columns, this change will mean that strings sent to the db without a timezone offset indicator (e.g. the AR-standard Time#to_s(:db) format) will be assumed to be in UTC, and not the system local time, and therefore *won''t* be incorrectly offset-shifted before storage. On Wed, Jan 20, 2010 at 3:09 AM, Colin Law <clanlaw@googlemail.com> wrote:> 2010/1/20 Geoff Buesing <gbuesing@gmail.com>: > > That sounds reasonable -- ActiveRecord could set the connection timezone > to > > UTC when AR::Base.default_timezone is :utc, and not set it to anything > > (i.e., assume the system local timezone is correct) when :local. > > What would this mean for what is actually stored in the database? > > Colin > > > Solutions have been offered before for accomodating timestamp with > timezone, > > but they have involved adding another timezone configuration and/or Rails > > TimeZone -> DB time zone mapping logic to the framework. This is much > > cleaner. > > Assuming this doesn''t take much code, and doesn''t negatively impact > > performance, I''m +1 on the idea. > > On Mon, Jan 18, 2010 at 6:18 PM, Jack Christensen <jack@jncsoftware.com> > > wrote: > >> > >> The current Rails practice with PostgreSQL is to use timestamp without > >> zone and use ActiveRecord''s time zone handling. This works fine for > >> Rails but makes it slightly inconvenient for ad hoc, local time queries > >> outside of Rails. I believe there is a way to get the best of both > >> worlds. If when creating the connection, ActiveRecord set the connection > >> time zone to UTC then Rails could handle time zones its way and other > >> clients could use PostgreSQL''s time zone handling. > >> > >> Is there any interest in a patch to this effect? > >> > >> > >> Jack > >> > >> -- > >> You received this message because you are subscribed to the Google > Groups > >> "Ruby on Rails: Core" group. > >> To post to this group, send email to rubyonrails-core@googlegroups.com. > >> To unsubscribe from this group, send email to > >> rubyonrails-core+unsubscribe@googlegroups.com<rubyonrails-core%2Bunsubscribe@googlegroups.com> > . > >> For more options, visit this group at > >> http://groups.google.com/group/rubyonrails-core?hl=en. > >> > >> > >> > > > > > > -- > > You received this message because you are subscribed to the Google Groups > > "Ruby on Rails: Core" group. > > To post to this group, send email to rubyonrails-core@googlegroups.com. > > To unsubscribe from this group, send email to > > rubyonrails-core+unsubscribe@googlegroups.com<rubyonrails-core%2Bunsubscribe@googlegroups.com> > . > > For more options, visit this group at > > http://groups.google.com/group/rubyonrails-core?hl=en. > > > > > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Core" group. > To post to this group, send email to rubyonrails-core@googlegroups.com. > To unsubscribe from this group, send email to > rubyonrails-core+unsubscribe@googlegroups.com<rubyonrails-core%2Bunsubscribe@googlegroups.com> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-core?hl=en. > > > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
On 1/19/2010 7:23 PM, Michael Koziarski wrote:>> Assuming this doesn''t take much code, and doesn''t negatively impact >> performance, I''m +1 on the idea. >> > Seems reasonable to me too. Please do try this out. >I uploaded a patch against master with tests. https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/3777-patch-postgresql-timestamp-with-zone-type-support Jack> >> On Mon, Jan 18, 2010 at 6:18 PM, Jack Christensen <jack@jncsoftware.com> >> wrote: >> >>> The current Rails practice with PostgreSQL is to use timestamp without >>> zone and use ActiveRecord''s time zone handling. This works fine for >>> Rails but makes it slightly inconvenient for ad hoc, local time queries >>> outside of Rails. I believe there is a way to get the best of both >>> worlds. If when creating the connection, ActiveRecord set the connection >>> time zone to UTC then Rails could handle time zones its way and other >>> clients could use PostgreSQL''s time zone handling. >>> >>> Is there any interest in a patch to this effect? >>> >>> >>> Jack >>> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "Ruby on Rails: Core" group. >>> To post to this group, send email to rubyonrails-core@googlegroups.com. >>> To unsubscribe from this group, send email to >>> rubyonrails-core+unsubscribe@googlegroups.com. >>> For more options, visit this group at >>> http://groups.google.com/group/rubyonrails-core?hl=en. >>> >>> >>> >>> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "Ruby on Rails: Core" group. >> To post to this group, send email to rubyonrails-core@googlegroups.com. >> To unsubscribe from this group, send email to >> rubyonrails-core+unsubscribe@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/rubyonrails-core?hl=en. >> >> >> > > >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.