PostgreSQL time zones

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

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.

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.

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

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.

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