Precision of DateTime fields, when specified, do not match the DB schema

[jrs-kiavi]

Description

When an object is created by FactoryBot, and a value with a greater precision than permitted by the DB schema is specified for one of its DateTime fields, the value for that field does not reflect the precision specified by the DB schema until the record is reloaded.

Reproduction Steps

Specs to reproduce:

describe 'FactoryBot' do
  context 'when a DateTime field is specified with greater precision than allowed by the DB' do
    let(:nanosecond_precision_datetime) { DateTime.parse("2025-01-01T11:11:11.123456789") }
    let(:microsecond_precision_datetime) { DateTime.parse("2025-01-01T11:11:11.123456") }
    let(:identity) { create(:identity, updated_at: nanosecond_precision_datetime) }

    it 'does not truncate the DateTime to the DB precision until the record is reloaded' do
      expect(identity.updated_at).to eq(nanosecond_precision_datetime)
      identity.reload
      expect(identity.updated_at).to eq(microsecond_precision_datetime)
    end
  end

  context "when a DateTime field's value is generated by FactoryBot / ActiveRecord" do
    let(:identity) { create(:identity) }

    it 'returns the same value both before and after reloading the record' do
      updated_at_before_reload = identity.updated_at
      expect(identity.updated_at).to eq(updated_at_before_reload)
      identity.reload
      expect(identity.updated_at).to eq(updated_at_before_reload)
    end
  end
end

Relevant part of the DB schema:

ActiveRecord::Schema[7.0].define do
  create_table "identities", id: { type: :string, limit: 12 }, force: :cascade do |t|
     t.datetime "updated_at", precision: nil, null: false

Expected behavior

The value for updated_at in the first spec should be at microsecond precision both before and after reloading.

Actual behavior

Calling reload on the record changed the value of the updated_at field.

System configuration

factory_bot version: 6.2.0
rails version: 7.0.8.6
ruby version: 3.3.4

[jrs-kiavi]

It looks like this is also how ActiveRecord models work when constructed directly (the spec below also passes). FactoryBot could work around this by reloading the record before returning the object, but doing that would incur a runtime cost that's unnecessary in most cases. Perhaps an alternative create method could be created which does that?

describe 'ActiveRecord' do
  context 'when a DateTime field is specified with greater precision than allowed by the DB' do
    let(:nanosecond_precision_datetime) { DateTime.parse("2025-01-01T11:11:11.123456789") }
    let(:microsecond_precision_datetime) { DateTime.parse("2025-01-01T11:11:11.123456") }

    it 'does not truncate the DateTime to the DB precision until the record is reloaded' do
      constructed = Identity.create!(updated_at: nanosecond_precision_datetime)
      expect(constructed.updated_at).to eq(nanosecond_precision_datetime)
      constructed.reload
      expect(constructed.updated_at).to eq(microsecond_precision_datetime)
    end
  end
end

[jrs-kiavi]

It looks like overriding to_create would allow someone to work around this on a per-factory basis: https://thoughtbot.github.io/factory_bot/custom-methods-to-persist-objects/summary.html

[neilvcarvalho]

Looks like that only happens when precision is nil. I was trying to reproduce that without using Factory Bot, in a User model with this database schema (removing other columns):

  create_table "users", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.datetime "disabled_at", precision: nil
  end

If I set a nanosecond precision datetime to the created_at, updated_at, current_sign_in_at, and last_sign_in_at, it truncates the datetime to microsecond precision. disabled_at, however, isn't truncated, only when it's saved and reloaded.

The schema when reading using psql:

                                                                                                                      Table "public.users"
           Column            |              Type              | Collation | Nullable |                                                                                  Default
-----------------------------+--------------------------------+-----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 created_at             | timestamp(6) without time zone |           | not null |
 updated_at             | timestamp(6) without time zone |           | not null |
 current_sign_in_at     | timestamp(6) without time zone |           |          |
 last_sign_in_at        | timestamp(6) without time zone |           |          |
 disabled_at            | timestamp without time zone    |           |          |

Just as a curiosity, I noticed that the precision: nil column was created as a timestamp, while the others were created as datetime on their migrations. That's a weird Rails inconsistency.