how to change rails migration t.timestamps to use `timestamp(0) without timezone` in postgres

Question

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

Answer 1

I believe I've figure it out!

I started looking into what NATIVE_DATABASE_TYPES were bring set by print out the variable from the console

Rails c
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES

Result: {:primary_key=>"bigserial primary key", :string=>{:name=>"character varying"}, :text=>{:name=>"text"}, :integer=>{:name=>"integer", :limit=>4}, :float=>{:name=>"float"}, :decimal=>{:name=>"decimal"}, :datetime=>{:name=>"timestamp"}, :time=>{:name=>"time"}, :date=>{:name=>"date"}, :daterange=>{:name=>"daterange"}, :numrange=>{:name=>"numrange"}, :tsrange=>{:name=>"tsrange"}, :tstzrange=>{:name=>"tstzrange"}, :int4range=>{:name=>"int4range"}, :int8range=>{:name=>"int8range"}, :binary=>{:name=>"bytea"}, :boolean=>{:name=>"boolean"}, :xml=>{:name=>"xml"}, :tsvector=>{:name=>"tsvector"}, :hstore=>{:name=>"hstore"}, :inet=>{:name=>"inet"}, :cidr=>{:name=>"cidr"}, :macaddr=>{:name=>"macaddr"}, :uuid=>{:name=>"uuid"}, :json=>{:name=>"json"}, :jsonb=>{:name=>"jsonb"}, :ltree=>{:name=>"ltree"}, :citext=>{:name=>"citext"}, :point=>{:name=>"point"}, :line=>{:name=>"line"}, :lseg=>{:name=>"lseg"}, :box=>{:name=>"box"}, :path=>{:name=>"path"}, :polygon=>{:name=>"polygon"}, :circle=>{:name=>"circle"}, :bit=>{:name=>"bit"}, :bit_varying=>{:name=>"bit varying"}, :money=>{:name=>"money"}, :interval=>{:name=>"interval"}, :oid=>{:name=>"oid"}

turns out that timestamp was never actually set before I started including it with my

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

What was being included thought was datetime and I realized that timestamp was an alias of datetime.

I changed the NATIVE_DATABASE_TYPES merge to look like this...

require "active_record/connection_adapters/postgresql_adapter"

module ActiveRecord
 module ConnectionAdapters
   class PostgreSQLAdapter
     NATIVE_DATABASE_TYPES.merge!(
       datetime: { name: "timestamp", limit:0 }
     )
   end
 end
end

I ran my migration and the columns were successfully set to timestamp(0) without timezone

Answer 2

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 Rails console. It is meant to work in a migration 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 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 (although I haven't tested it).