@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.

@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

@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]

@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
=> []

@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

@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.

hi @ghiculescu sorry for mentiong

why there is no documentation for this config?

I think it should be mentioned here https://guides.rubyonrails.org/active_record_postgresql.html
but I only see the doc about this in here https://guides.rubyonrails.org/configuring.html#activerecord-connectionadapters-postgresqladapter-datetime-type

and where to put this config?

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

in application.rb?

and also I got confused by this line in your post

If you change this setting in an existing app, you should immediately call bin/rails db:migrate to ensure your schema.rb

I have run rails db:migrate but nothing changing at all, do you mean I should make a columntype changes manually?

like

change_column(:users, :created_at :timestamptz)
change_column(:users, :updated_at :timestamptz)

I think it should be mentioned here https://guides.rubyonrails.org/active_record_postgresql.html

I agree. Would you like to make a PR?

The snippet from https://guides.rubyonrails.org/configuring.html#activerecord-connectionadapters-postgresqladapter-datetime-type should work anywhere in config/application.rb.

If there were no changes to schema.rb, then that's fine. I think that is only needed if you were manually using timestamptz columns before switching, or something like that.

@ghiculescu my mistake

doing rails db:migrate after change config to use timestampz

# config/application.rb

ActiveSupport.on_load(:active_record_postgresqladapter) do
  self.datetime_type = :timestamptz
end

indeed changing something on schema.rb

the changes are

from this

ActiveRecord::Schema[7.1].define(version: 2024_07_31_041319) do
  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"

  create_table "blogs", force: :cascade do |t|
    t.string "title"
    t.text "content"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

end

to this

  create_table "blogs", force: :cascade do |t|
    t.string "title"
    t.text "content"
    t.timestamp "created_at", precision: 6, null: false
    t.timestamp "updated_at", precision: 6, null: false
  end

what I mean by not changing after doing rails db:migrate is the table structure,
when I do rails dbconsole and do \d+ blogs
its still using timestamp(6) without time zone instead timestamp(6) with time zone for created_at and updated_at

is that intentional?

Yes that’s intentional. It won’t rebuild your database, it just applies to new columns going forward. We need to reset the schema file to ensure you’re generating a test database correctly.

Hi @ghiculescu and @laptopmutia, I created this Rails Guides Active Record PostgreSQL PR to document why and how to use this new configuration option. #53687

This was based on generating a new Rails 8, realizing this data type is not the default, then realizing I didn't know how to configure my app to use it. I found this PR, then saw the suggestion it's not document yet, and a suggestion to put it into the Rails Guides page. I picked the data types section.

Let me know what you think!

Hi @ghiculescu thanks for this PR.

If I don't add :timestamp to time_zone_aware_attributes this breaks production for existing columns :

ActiveSupport.on_load(:active_record_postgresqladapter) do
  self.datetime_type = :timestamptz
  
  # Had to add this
  ActiveRecord::Base.time_zone_aware_types << :timestamp
end

I was confused how when I set self.datetime_type = :timestamptz all my created_at/updated_at were in UTC.
After researching it was because of these lines:

I think technically what you wrote is correct for fresh applications (from scratch) but for existing applications, I think it's worth mentionning that ActiveRecord::Base.time_zone_aware_types << :timestamp needs to be added until all columns have been migrated. and since timestamp was transformed into datetime and datetime is time zone aware, I believe this is a breaking change

I don't know if I should create a documentation PR, or add timestamp to time_zone_aware_types because technically for all rails applicaitons, timestamp is equal to datetime