ActiveRecord: Store Milliseconds (or Microseconds) in Timestamps with Rails / MySQL

README.md

ActiveRecord: Store Milliseconds (or Microseconds) in DateTimes or Timestamps with Rails / MySQL

Milliseconds in your DateTimes or Timestamps.

We got 'em, you want 'em.

NOTE: only MySQL 5.6.4 and above supports DATETIME's with more precision than a second. For reference see MySQL 5.6.4 Changelog

Why

Shit needs to be PRECISE

LICENSE

MIT

datetime_precision_migration.rb

class DatetimePrecisionMigration < ActiveRecord::Migration
  def change
    reversible do |change|
      # Migrate Up
      change.up do
        table_columns do |table, column|
          # MySQL supports time precision down to microseconds -- DATETIME(6)
          change_column table, column, :datetime, limit: 6
        end
      end

      # Migrate Down
      change.down do
        table_columns do |table, column|
          change_column table, column, :datetime
        end
      end

      # NOTE: only MySQL 5.6.4 and above supports DATETIME's with more precision
      # than a second. See https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-4.html#mysqld-5-6-4-fractional-seconds
    end
  end

  private

  def table_columns
    # Iterate through all tables in this environment's database
    ActiveRecord::Base.connection.tables.each do |table|
      # Iterate through all columns in table
      ActiveRecord::Base.connection.columns(table).each do |column|
        yield table, column.name if column.type == :datetime && block_given?
      end
    end
  end

end

mysql.rb

# Save this in config/initializers/mysql.rb (or whatever you want, filename doesn't matter.)

# Creates DATETIME(6) column types by default which support microseconds.
#
# Without it, only regular (second precise) DATETIME columns are created.
module ActiveRecord::ConnectionAdapters
  AbstractMysqlAdapter::NATIVE_DATABASE_TYPES[:datetime][:limit] = 6
end

time.rb

# Save this in config/initializers/time.rb (or whatever you want, filename doesn't matter.)

# NOTE: Apparently, this initializer is not necessary with Rails 4.2.5 and up. 
# It just works with the correct database type DATETIME(6). 

# Where 6N is the number of places after the decimal (.) 
# For less precision (eg. miliseconds), change 6N to 3N  
Time::DATE_FORMATS[:db] = '%Y-%m-%d %H:%M:%S.%6N'

Thanks for this Mark. I'm on Rails 4.2.6 and I do not find https://gist.github.com/MarkMurphy/93adca601b05acffb8b5601df09f66df#file-time-rb-L3 to be the case. I'm still getting some_obj.created_at.to_s(:db) #=> "2016-08-18 18:10:19". I did a quick search of the Rails GitHub project but wasn't able to find where this was changed.

works great! thanks!