PostgreSQL: handle `timestamp with time zone` columns correctly in `schema.rb` by ghiculescu · Pull Request #41395 · rails/rails

ghiculescu · 2021-02-10T18:02:56Z

Found this issue while working on #41084

Currently if you have a timestamp with time zone column, and you run rake db:schema:dump, it will be output as a t.datetime. This is wrong because when you run rake db:schema:load, the column will be recreated as a timestamp without time zone.

The fix is to add a new column type, t.timestamptz. This behaves exactly the same as before, the only change is what native type it is converted to during schema loads.

This means that if you run rake db:migrate with this code, you will see changes to your schema.rb if the database you were dumping from includes timestamp with time zone columns.

…chema.rb` Found this issue while working on rails#41084 Currently if you have a `timestamp with time zone` column, and you run `rake db:schema:dump`, it will be output as a `t.datetime`. This is wrong because when you run `rake db:schema:load`, the column will be recreated as a `timestamp without time zone`. The fix is to add a new column type, `t.timestamptz`. This behaves exactly the same as before, the only change is what native type it is converted to during schema loads.

ghiculescu · 2021-02-10T18:05:17Z

activerecord/test/cases/schema_dumper_test.rb

+      output = perform_schema_dump
+      assert output.include?('t.datetime "default_format"')
+      assert output.include?('t.datetime "without_time_zone"')
+      assert output.include?('t.timestamptz "with_time_zone"')


without the changes in this PR, this test would fail. output.include?('t.datetime "with_time_zone"') would be true. the problem then is when you recreate the table (eg. in a test environment), the column is created as timestamp, not timestamptz.

ghiculescu · 2021-02-10T18:08:47Z

activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb

          register_class_with_precision m, "time", Type::Time
-          register_class_with_precision m, "timestamp", OID::DateTime
+          register_class_with_precision m, "timestamp", OID::Timestamp
+          register_class_with_precision m, "timestamptz", OID::TimestampWithTimeZone


removing line 535 and adding this is the fix

rafaelfranca · 2021-02-10T19:55:16Z

Thank you!

…one` In rails#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](https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_timestamp_.28without_time_zone.29). 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`. ```ruby 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 rails#41395 which set some groundwork (and added some tests) for this. - This reverts some of rails#15184. rails#15184 alluded to issues in XML serialization, but I couldn't find any related tests that this broke.

bbugh · 2022-02-02T15:53:34Z

I'm reading through all of the different issues related to the timestamptz on Rails 7, and this seems to be the most relevant one to ask this in case it's not a bug

When using change_column to convert a datetime to a timestamptz, it's not updating the schema.rb to timestamptz.

Given Rails 7.0.1 and

# config/initializers/database.rb
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.datetime_type = :timestamptz

When running a migration:

change_column :roles, :created_at, :timestamptz
change_column :roles, :updated_at, :timestamptz

-- change_column("roles", "created_at", :timestamptz)
   -> 0.0005s
-- change_column("roles", "updated_at", :timestamptz)
   -> 0.0004s

The schema.rb still has datetime as the column type:

  create_table "roles", force: :cascade do |t|
-   t.datetime "created_at", precision: 6, null: false
-   t.datetime "updated_at", precision: 6, null: false
+   t.datetime "created_at", null: false
+   t.datetime "updated_at", null: false

However, the columns in the database are now changed to timestamp with time zone.

                                        Table "public.roles"
    Column     |           Type           | Collation | Nullable |
---------------+--------------------------+-----------+----------+
 created_at    | timestamp with time zone |           | not null |
 updated_at    | timestamp with time zone |           | not null |

Is it expected that the column type in schema.rb is still datetime?

ghiculescu · 2022-02-02T16:08:52Z

Yes, that is correct.

Since your datetime_type is now :timestamptz, using t.datetime in a migration will give you a timestamptz column. So in schema.rb it uses t.datetime to match that.

The idea is that unless you explicitly ask for a t.timestamp, you'll get a timestamptz as that's the default you have set.

bbugh · 2022-02-02T16:18:37Z

Thank you for confirming, and for all of the work you did on this!

ghiculescu · 2022-03-02T19:54:59Z

There's a potential issue with this PR - #44601

rails#41395 added support for the `timestamptz` type on the Postgres adapter. As we found [here](rails#41084 (comment)) this causes issues because in some scenarios the new type is not considered a time zone aware attribute, meaning values of this type in the DB are presented as a `Time`, not an `ActiveSupport::TimeWithZone`. This PR fixes that by ensuring that `timestamptz` is always a time zone aware type, for Postgres users.

rails-bot bot added the activerecord label Feb 10, 2021

ghiculescu mentioned this pull request Feb 10, 2021

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

Merged

ghiculescu commented Feb 10, 2021

View reviewed changes

rafaelfranca merged commit 0dda445 into rails:main Feb 10, 2021

ghiculescu deleted the pg-schema-dump-timestamptz branch February 10, 2021 20:24

ghiculescu mentioned this pull request Feb 16, 2021

PostgreSQL: support custom enum types #41469

Merged

rhymes mentioned this pull request Feb 24, 2021

PostgreSQL: change timestamp columns to timestamp with timezone forem/forem#11596

Closed

aried3r mentioned this pull request Sep 7, 2021

Another take on 'Database and Ruby have inconsistent time zone info' ankane/groupdate#222

Closed

ghiculescu mentioned this pull request Mar 2, 2022

Add timestamptz as a time zone aware type for PostgreSQL #44601

Merged

shiroemons mentioned this pull request Sep 4, 2022

Support timestamptz ridgepole/ridgepole#394

Closed

Sebmono mentioned this pull request Jan 19, 2026

[PR] Active Record + PostgreSQL: native support for timestamp with time zone Sandgarden-Demo/rails#15

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

PostgreSQL: handle `timestamp with time zone` columns correctly in `schema.rb`#41395

PostgreSQL: handle `timestamp with time zone` columns correctly in `schema.rb`#41395
rafaelfranca merged 1 commit intorails:mainfrom
ghiculescu:pg-schema-dump-timestamptz

ghiculescu commented Feb 10, 2021 •

edited

Loading

Uh oh!

ghiculescu Feb 10, 2021

Uh oh!

ghiculescu Feb 10, 2021

Uh oh!

rafaelfranca commented Feb 10, 2021

Uh oh!

bbugh commented Feb 2, 2022

Uh oh!

ghiculescu commented Feb 2, 2022

Uh oh!

bbugh commented Feb 2, 2022

Uh oh!

ghiculescu commented Mar 2, 2022

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

ghiculescu commented Feb 10, 2021 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

ghiculescu Feb 10, 2021

Choose a reason for hiding this comment

Uh oh!

ghiculescu Feb 10, 2021

Choose a reason for hiding this comment

Uh oh!

rafaelfranca commented Feb 10, 2021

Uh oh!

bbugh commented Feb 2, 2022

Uh oh!

ghiculescu commented Feb 2, 2022

Uh oh!

bbugh commented Feb 2, 2022

Uh oh!

ghiculescu commented Mar 2, 2022

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

ghiculescu commented Feb 10, 2021 •

edited

Loading