如何在Postgres中更改Rails迁移t.timestamps以使用不带时区的timestamp（0） [英] how to change rails migration t.timestamps to use `timestamp(0) without timezone` in postgres

问题描述

我正在尝试找出如何更改 t.timestamps 在Rails迁移中使用的本机数据类型。以postgres结尾的默认类型为不带时区的时间戳。我想要的是 timestamp（0）（没有时区）。

我想更改本地数据类型，以便在创建新表并在迁移中使用 t.timestamps 时，它会自动创建正确的时间戳数据类型。

我需要 timestamp（0）（没有时区），因为我的rails应用程序与laravel应用程序共享其DB，并且两个应用程序都可以插入数据。由于rails不会使用毫秒/ laravel，并且（截至2018-10-23）laravel似乎没有办法支持拥有包含不同时间戳格式的表（ Ymd H：i：su 与 Ymd H：i：s ），而不必关闭模型中的时间戳，本质上禁用了自动管理它们，我想让数据库强制使用单一格式（ Ymd H：i：s ）。

有关更多详细信息，请问其他问题：

解决方案

这是一个解决方案。它将Rails中的默认时间戳记精度（包括迁移在内）更改为两个时间戳记，在PostgreSQL中更改为一秒。这既不容易也不简单，但是可以在PostgreSQL上使用Rails 5.2。

我认为初始化程序应该放在 config / initializers / （不在环境中）。

写入以下文件。

 ＃./config/initializers/arbitrary.rb 
 
需要 active_record / connection_adapters / abstract / schema_definitions.rb 
需要 active_record / connection_adapters / abstract_adapter  
需要 active_record / connection_adapters /抽象/ schema_statements 
需要 active_record / connection_adapters / postgresql / schema_statements 
需要 active_record / connection_adapters / postgresql_adapter 
 
模块ActiveRecord 
模块ConnectionAdapters 
＃覆盖/abstract/schema_definitions.rb中的方法
类TableDefinition 
 def timestamps（** options）
 options [：null] = false如果options [：null] .nil？ 
 
列（：created_at，：datetime0，选项）
列（：updated_at，：datetime0，选项）
结束
结束
 
 ＃覆盖/abstract/schema_statements.rb 
模块SchemaStatements 
 def add_timestamps（table_name，options = {}）
 options [：null] = false，如果options [：null] .nil ？ 
 
 add_column table_name，：created_at，：datetime0，选项
 add_column table_name，：updated_at，：datetime0，选项
 end 
 end 
 
 ＃覆盖/postgresql/schema_statements.rb 
模块PostgreSQL 
模块SchemaStatements 
 def add_timestamps_for_alter（table_name，options = {}）
 [add_column_for_alter（table_name，：created_at，： datetime0，选项），add_column_for_alter（表名，：updated_at，：datetime0，选项）] 
 end 
 end 
 end 
 
＃修改/ postgresql_adapter中的常量和方法.rb 
类PostgreSQLAdapter 
 alias_method：initialize_type_map_orig，：initialize_type_map if！ self.method_defined？（：initialize_type_map_orig）
 NATIVE_DATABASE_TYPES [：datetime0] = {名称： timestamp（0）} 
 
 private 
 def initialize_type_map（m = type_map）
 register_class_with_precision_t0 m， timestamp0，OID :: DateTime 
 initialize_type_map_orig（m）
 end 
 
 def register_class_with_precision_t0（mapping，key，klass）
 mapping.register_type （键）做| * args | 
 klass.new（精度：0）
结束
结束
结束
结束
结束
  

以下是示例迁移文件。

 ＃db / migrate / 20181023182238_create_articles.rb 
 class CreateArticles< ActiveRecord :: Migration [5.2] 
 def change 
 create_table：articles | t | 
 t.string：title 
 t.timestamps 
 end 
 end 
 end 
  

迁移（ bin / rails db：migrate ）创建表 articles PostgreSQL数据库中 timestamp（0）（无时区）的两个时间戳列。

执行的SQL是

 创建表 articles（
 id bigserial主键，
 title字符变化，
 created_at时间戳（0）不为空，
 updated_at时间戳（0）不为空）； 
  

注意

我已经确认了两次迁移在Raisl控制台中创建表并进行数据更新。它的目的还在于更新表，但我尚未对其进行测试。

稍加调整，它也可以在其他数据库中使用。

基本上，以上代码定义了一个新的Rails类型 timestamp0 ， timestamps（ ）（即 created_at 和 updated_at ）。
如果要使时间戳的任何其他列相同（即，数据库中没有亚秒精度），请在迁移中指定 timestamp0 ，应该可以工作（尽管我还没有测试）。

I'm trying to figure out how to change the native data type that t.timestamps uses in a rails migration. Default type that ends up in postgres is timestamp without timezone. What I would like instead is timestamp(0) without timezone.

I'd like to change the native data type so that when a new table is created and t.timestamps is used in the migration, it automatically creates the correct timestamp data type.

I require timestamp(0) without timezone because my rails application shares its DB with a laravel application and both applications can insert data. Due to the fact that rails uses milliseconds/laravel does not, and there doesn't seem to be a way ( as of 2018-10-23 ) for laravel to support having a table that contains different formats of timestamps (Y-m-d H:i:s.u vs Y-m-d H:i:s) without having to turn off timestamps in the model, essentially disabling the auto management of them, I'd like to have the database enforce the use of a single format(Y-m-d H:i:s).

For more details please my other question: Is there a way to change Rails default timestamps to Y-m-d H:i:s (instead of Y-m-d H:i:s.u) or have laravel ignore decimal portion of Y-m-d H:i:s.u?

So I want to use timestamp(0) to truncate the milliseconds and not have to think about setting the tables timestamp types correctly when creating a new table, as the native type would already be timestamp(0)

I've tried this

./config/environments/initializers

require "active_record/connection_adapters/postgresql_adapter"

module ActiveRecord
 module ConnectionAdapters
   class PostgreSQLAdapter
     NATIVE_DATABASE_TYPES.merge!(
      timestamp: { name: "timestamp(0) without timezone" }
     )
   end
 end
end

and a migration like

class ChangeTimestampTypesToTimestamp0 < ActiveRecord::Migration[5.2]
  def change
    create_table :test, id: :uuid, default: -> { "gen_random_uuid()" } do|t|
      t.string :name, null: false

      t.timestamps
    end
  end
end

but that did not work.

I also tried to change the timestamp to use timestampz with the same migration above as a sanity check, still no luck...

require "active_record/connection_adapters/postgresql_adapter"

module ActiveRecord
 module ConnectionAdapters
   class PostgreSQLAdapter
     NATIVE_DATABASE_TYPES.merge!(
       timestamp: { name: "timestamptz" }
     )
   end
 end
end

解决方案

Here is a solution. It alters the default timestamp precision in Rails, including for migration, for the two timestamps to one second accuracy in PostgreSQL. It's neither easy nor simple, but works for Rails 5.2 with PostgreSQL.

I think the initializer should be placed in config/initializers/ (not in environments).
Write the following file.

# ./config/initializers/arbitrary.rb

require "active_record/connection_adapters/abstract/schema_definitions.rb"
require "active_record/connection_adapters/abstract_adapter"
require "active_record/connection_adapters/abstract/schema_statements"
require "active_record/connection_adapters/postgresql/schema_statements"
require "active_record/connection_adapters/postgresql_adapter"

module ActiveRecord
  module ConnectionAdapters
    # Overwrites a method in /abstract/schema_definitions.rb
    class TableDefinition
      def timestamps(**options)
        options[:null] = false if options[:null].nil?

        column(:created_at, :datetime0, options)
        column(:updated_at, :datetime0, options)
      end
    end

    # Overwrites a method in /abstract/schema_statements.rb
    module SchemaStatements
      def add_timestamps(table_name, options = {})
        options[:null] = false if options[:null].nil?

        add_column table_name, :created_at, :datetime0, options
        add_column table_name, :updated_at, :datetime0, options
      end
    end

    # Overwrites a method in /postgresql/schema_statements.rb
    module PostgreSQL
      module SchemaStatements
        def add_timestamps_for_alter(table_name, options = {})
          [add_column_for_alter(table_name, :created_at, :datetime0, options), add_column_for_alter(table_name, :updated_at, :datetime0, options)]
        end
      end
    end

    # Modifies a constant and methods in /postgresql_adapter.rb
    class PostgreSQLAdapter
      alias_method :initialize_type_map_orig, :initialize_type_map if ! self.method_defined?(:initialize_type_map_orig)
      NATIVE_DATABASE_TYPES[:datetime0] = { name: "timestamp(0)" }

      private    
        def initialize_type_map(m = type_map)
          register_class_with_precision_t0 m, "timestamp0", OID::DateTime
          initialize_type_map_orig(m)
        end

        def register_class_with_precision_t0(mapping, key, klass)
          mapping.register_type(key) do |*args|
            klass.new(precision: 0)
          end
        end
    end
  end
end

Here is an example migration file.

# db/migrate/20181023182238_create_articles.rb
class CreateArticles < ActiveRecord::Migration[5.2]
  def change
    create_table :articles do |t|
      t.string :title
      t.timestamps
    end
  end
end

Migration (bin/rails db:migrate) creates a table articles with the two timestamps columns of timestamp(0) (without timezone) in the PostgreSQL database.

The SQL executed is this:

CREATE TABLE "articles" (
  "id" bigserial primary key, 
  "title" character varying, 
  "created_at" timestamp(0) NOT NULL,
  "updated_at" timestamp(0) NOT NULL);

Note

I have confirmed both migration to create a table and data updating works in Raisl console. It is meant to work in mingration to update a table, too, but I haven't tested it.

With a bit more tweaking it would work in other databases as well.

Basically the code above defines a new Rails type timestamp0, to which timestamps() (which is created_at and updated_at) is assigned. If you want for any other columns of timestamp to be the same (i.e., no sub-second precision in the DB), specify timestamp0 in your migration and it should work (though I haven't tested).

这篇关于如何在Postgres中更改Rails迁移t.timestamps以使用不带时区的timestamp（0）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！