Active Record + PostgreSQL: native support for timestamp with time zone

[ghiculescu]

In #21126 it was suggested to make "timestamp with time zone" the default type for datetime columns in PostgreSQL. This is in line with PostgreSQL best practices. This PR lays some groundwork for that.

This PR adds a configuration option, ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.datetime_type. The default is :timestamp which preserves current Rails behavior of using "timestamp without time zone" when you do t.datetime in a migration. If you change it to :timestamptz, you'll get "timestamp with time zone" columns instead.

If you change this setting in an existing app, you should immediately call bin/rails db:migrate to ensure your schema.rb file remains correct. If you do so, then existing columns will not be impacted, so for example if you have an app with a mixture of both types of columns, and you change the config, schema dumps will continue to output the correct types.

This PR also adds two new types that can be used in migrations: t.timestamp and t.timestamptz.

ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.datetime_type = :timestamp # default value is :timestamp

create_table("foo1") do |t|
  t.datetime :default_format # "timestamp without time zone"
  t.timestamp :without_time_zone # "timestamp without time zone"
  t.timestamptz :with_time_zone # "timestamp with time zone"
end

ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.datetime_type = :timestamptz

create_table("foo2") do |t|
  t.datetime :default_format # "timestamp with time zone" <-- note how this has changed!
  t.timestamp :without_time_zone # "timestamp without time zone"
  t.timestamptz :with_time_zone # "timestamp with time zone"
end

ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:my_custom_type] = { name: "custom_datetime_format_i_invented" }
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.datetime_type = :my_custom_type

create_table("foo3") do |t|
  t.datetime :default_format # "custom_datetime_format_i_invented"
  t.timestamp :without_time_zone # "timestamp without time zone"
  t.timestamptz :with_time_zone # "timestamp with time zone"
end

Notes

• This PR doesn't change the default datetime format. The default is still "timestamp without time zone". A future PR could do that, but there was enough code here just getting the config option right.
• See also PostgreSQL: handle timestamp with time zone columns correctly in schema.rb #41395 which set some groundwork (and added some tests) for this.
• This reverts some of Remove :timestamp column type #15184. Remove :timestamp column type #15184 alluded to issues in XML serialization, but I couldn't find any related tests that this broke.

[ghiculescu]

@rafaelfranca tagging you in since you commented here #21126 (comment) (but let me know if I should tag someone else - you get tagged in for everything). How's this looking so far?

I'm not confident about the CI bit rails/buildkite-config#11 and don't really know how to test that it will work. I have been running POSTGRESQL_DATETIME_TYPE=timestamp bundle exec rake test:postgresql locally to confirm tests pass. The way that's implemented is fairly bad too, but I couldn't find/think of any better approaches.

[Dearest]

@ghiculescu
I'll elaborate on the problem I'm having and I hope you can help me.

I am trying to upgrade Rails 6.x to Rails 7.x.

I have a project that connects to multiple databases. In these databases some time fields are of type timestamp and some fields are timestamptz. This means that I have both timestamp and timestamptz in the databases I am connected to.
Now I have a model called Post, which has a published_at attribute, and this field is of type timestamptz in the database (this column is use sql create, not rails migration.)

# Rails6.1.4.2
Post.last.published_at.class
# ==> ActiveSupport:: TimeWithZone
Post.last.published_at.to_s
# ==> 2022-02-23 10:33:36

everything is fine
When I upgraded to Rails 7. same model, same fields

# Rails 7.0.1
Post.last.published_at.class
# ==> Time
Post.last.published_at.to_s
# ==> 2022-02-23 02:33:36 UTC

To summarize: Rails 6.x formats timestamp as ActiveSupport::TimeWithZone, but Rails 7.x formats the same field as Time. But I didn't see this discussed in the issue, so I don't know what went wrong

[ghiculescu]

@Dearest that executable test needs ActiveRecord::Base.time_zone_aware_attributes = true, then it will pass on Rails 6 or 7. (That is needed because time_zone_aware_attributes is set in a Railtie, which isn't run in the executable test case by default. But it is run when you start the app/console.)

I have a feeling I know what the issue is. Could you please get me the values of

ActiveRecord::Base.time_zone_aware_attributes
ActiveRecord::Base.time_zone_aware_types
ActiveRecord::Base.skip_time_zone_conversion_for_attributes

In both your Rails 6 and 7 versions. I have a feeling time_zone_aware_types will equal [:datetime, :time]. If it does, see if adding :timestamptz to that array fixes the issue, by doing this in an initializer

ActiveRecord::Base.time_zone_aware_types += [:timestamptz]

[Dearest]

@ghiculescu COOL! , I use this fix the issue

ActiveRecord::Base.time_zone_aware_types += [:timestamptz]

Before add ActiveRecord::Base.time_zone_aware_types += [:timestamptz]

Loading development environment (Rails 7.0.1)
[1] pry(main)> ActiveRecord::Base.time_zone_aware_attributes
=> true
[2] pry(main)> ActiveRecord::Base.time_zone_aware_types
=> [:datetime, :time]
[3] pry(main)> ActiveRecord::Base.skip_time_zone_conversion_for_attributes
=> []

Loading development environment (Rails 6.1.4.1)
[1] pry(main)> ActiveRecord::Base.time_zone_aware_attributes
=> true
[2] pry(main)> ActiveRecord::Base.time_zone_aware_types
=> [:datetime, :time]
[3] pry(main)> ActiveRecord::Base.skip_time_zone_conversion_for_attributes
=> []

[Dearest]

@ghiculescu But I got another issue
in Rails6. User.last.created_at.class is ActiveSupport::TimeWithZone. And I use to_s，I will get result like this

Loading development environment (Rails 6.1.4.1)
[1] pry(main)> User.last.created_at.to_s
=> "2021-12-08 18:13:34"

But Rails7. I will get string include time zone

Loading development environment (Rails 7.0.1)
[1] pry(main)> User.last.created_at.to_s
=> "2021-12-08 18:13:34 +0800"

Can I configure the behavior of to_s . I don't want to use the monkey patch to hack to_s method

[ghiculescu]

@Dearest is User.last.created_at.class the same in both Rails versions? What is the database type of User#created_at? And what is ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.datetime_type?

btw I see you're on 7.0.1. If you are upgrading now, you should try going straight to 7.0.2.2.