mackuba
July 27, 2026, 5:03pm
1
Hi! Could someone take a look at this PR? It’s meant to unify handling of timestamp precision across datetime, timestamp and timestamptz types in Postgres databases, which are currently not handled in the exact same way.
main ← mackuba:pg_timestamp_fixes
opened 02:56AM - 02 Jun 26 UTC
### Motivation / Background
While migrating my app from SQLite to Postgres so… me time ago, I've noticed some inconsistencies in how the precision is set in timestamp columns depending on how they're declared in the migration DSL. I made an example repo here that demonstrates the problem: https://github.com/mackuba/rails-timestamp-precision-demo.
It creates a table with 3 sets of 4 timestamp fields each: `datetime`, `timestamp`, and `timestamptz`, with `precision` set to 0, 6, `nil` and not specified. These columns all end up as either `timestamp` or `timestamptz` in the database, since `datetime` maps to one of those depending on the `datetime_type` setting, but where they differ is in the precision flags. I would have expected the 4 precision options to end up with the same result in each of the 3 sets, but they don't:
- the `timestamptz` set of columns have no precision at all, none of the 4 fields
- the `timestamp` and `timestamptz` with no precision set default to a precision `nil`, instead of 6 as `datetime` does
Note, this is all Postgres specific, since in MySQL `datetime` and `timestamp` are two very different column types.
### Detail
I've made a number of fixes in:
- `ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaStatements#type_to_sql`
- `ActiveRecord::ConnectionAdapters::PostgreSQL::ColumnMethods::TableDefinition#new_column_definition`
- `ActiveRecord::ConnectionAdapters::PostgreSQL::SchemaDumper#schema_precision`
which make the precision behave the same across Postgres columns declared as `datetime`, `timestamp` and `timestamptz`, both with `datetime_type` set to `:timestamptz` and with the default setting, in how the `schema.rb` is written and in what actually ends up in the database structure. Plus I've added some tests that I think should cover all of this.
### Additional information
I made all the changes in the `PostgreSQL` subclasses of the components, since these are all Postgres-specific things. Alternatively, some changes could be made in the abstract base class instead, e.g. the first commit could just be replaced with adding `:timestamptz` here to the base `SchemaStatements`:
```rb
elsif [:datetime, :timestamp, :time, :interval].include?(type) && precision ||= native[:precision]
```
since the `:interval`, which I think is also Postgres-specific, is already there. But it felt more appropriate to put it in the Postgres subclasses.
We could also move the `:interval` from there to the Postgres subclass now for consistency.
One thing to note is that this will create diffs in some people's `schema.rb` on update and after regenerating the schema, because of the changed defaults:
- if someone had a `t.timestamptz` field (or a `t.timestamp` field in `datetime_type = :timestamptz` mode), it would have no precision and be printed to `schema.rb` with no precision option, but now it will be dumped as `..., precision: nil`, since the default will now be 6
- in `datetime_type = :timestamptz` mode, if someone had a `t.timestamp` field with `precision: 6`, it will be dumped to `schema.rb` with the option omitted since that's now the default as in `datetime`