Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
ActiveRecord: Store Milliseconds (or Microseconds) in Timestamps with Rails / MySQL

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

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
# 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
# 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'
@agrberg

This comment has been minimized.

Copy link

@agrberg agrberg commented Aug 18, 2016

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.

@tgrtto

This comment has been minimized.

Copy link

@tgrtto tgrtto commented Jan 26, 2017

works great! thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.