Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Migrations do not support :precision for postgres timestamp. #3520

mattscilipoti opened this Issue Nov 4, 2011 · 9 comments


None yet
8 participants

Without this, it is very difficult to retrieve datetime fields in Rails. The database holds a higher precision than the time as a string. See #3519.

This is for Rails 3.1.1.

Tried both create_table and change_column.

create_table :test do |t|
    t.datetime :sent_at, :precision => 0 #ignore fractions of seconds
                                   Table "public.test"
 Column  |            Type             |                     Modifiers                     
 id      | integer                     | not null default nextval('test_id_seq'::regclass)
 sent_at | timestamp without time zone | 

That should be timestamp(0) without time zone.

I am working on a patch, but I felt I should announce the issue first.

Input is encouraged.

I have found the code that needs to be updated, but I am befuddled by the tests.

Also, I thought I would be updating the code that generates "timestamp with[out] time zone", but I could not find it.

This code does the job. Can someone help me with the tests?

Branch: 3-1-stable


# Maps logical Rails types to PostgreSQL-specific data types.
      def type_to_sql(type, limit = nil, precision = nil, scale = nil)
        case type.to_s
        when 'datetime'
          return super unless precision
          # sql_type could be either "timestamp with time zone" or "timestamp without time zone"
          # Either way, we want to insert the precision after "timestamp"
          super.gsub('timestamp', "timestamp(#{precision})")

        when 'integer'
          return 'integer' unless limit

          case limit
          when 1, 2; 'smallint'
          when 3, 4; 'integer'
          when 5..8; 'bigint'
          else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.")
          return super

@tenderlove tenderlove was assigned Nov 7, 2011

How we we set "with time zone" for (created_at and update_at) with t.timestamps in the migrations?

In Rails 2.3.8 we can do by "def native_database_types". But we can't set properly "with time zone" timestamps in migrations with Rails 3.1.0. Timestamps Always are created as "without time zone".

Adding manually create_at and update_at, gets the same result "without time zone"

Is "execute ... alter query..." in migration, the only way??

are news on this?

hardipe commented Jan 21, 2012

You can use :limit => 0 instead of :precision => 0

I know it's not the correct terminology to use :limit, but it's a bit better than writing raw SQL.

@mattscilipoti you should submit a PR request for this. This is pretty good stuff.

I would, but as I said above, the tests around this area of code don't make sense to me and no one has stepped forward to assist.


steveklabnik commented May 2, 2012

Pull requests don't have to be full, the idea is to get a conversation going around a chunk of code. As it is right now, manual patching has to go on, which discourages others from helping out.


tonywok commented Jun 21, 2012

I'm working on a pull request. The problem is a little deeper than I originally expected. The regular expressions that extracts out the precision and limit from the sql_type is incorrect.

Check out activerecord/lib/activerecord/connection_adapters/column.rb:256 for the regexp

It's always going to grab the 'X' out of timestamp(X) and assume it is a limit instead of a precision. Also, the regexp that extracts the precision is completely ignoring timestamps.

I currently have it such that postgres is receiving the correct type information. Now I'm working on what ActiveRecord thinks the type is.

You can follow my commits if interested. Hopefully I'll have a pull request soon.

@tonywok tonywok added a commit to tonywok/rails that referenced this issue Jun 22, 2012

@tonywok tonywok Allow precision option for postgresql datetimes
This patch addresses the difficulty of retrieving datetime fields. By default, the database holds a higher precision than the time as a String.

This issue is discussed at length at the following links:
- [#3519](rails#3519)
- [#3520](rails#3520)

Also, kudos to @mattscilipoti

rafaelfranca commented Jun 22, 2012

This should be fixed by 96ce1f2.

Closing this now.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment