Skip to content

Instantly share code, notes, and snippets.

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

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

Milliseconds in your Timestamps.

We got 'em, you want 'em.

Why

Shit needs to be PRECISE

LICENSE

MIT

class MillisecondsMigration < ActiveRecord::Migration
# Include non default date stamps here
# Key :table_name
# value [:column_names]
# NOTE: only MySQL 5.6.4 and above supports DATETIME's with more precision than a second.
TABLES_AND_COLUMNS = {
# Your :table_names here
}
STANDARD_ACTIVE_RECORD_COLUMNS = [:created_at, :updated_at]
TABLE_AND_COLUMNS.each {|k,v| v.concat(STANDARD_ACTIVE_RECORD_COLUMNS)}
def up
TABLE_AND_COLUMNS.each do |table, columns|
columns.each do |column|
# MySQL supports time precision down to microseconds -- DATETIME(6)
change_column table, column, :datetime, limit: 3
end
end
end
def down
TABLE_AND_COLUMNS.each do |table, columns|
columns.each do |column|
echange_column table, column, :datetime
end
end
end
end
# Save this in config/initializers/WHATEVER.rb
class Time
# Ruby will complain that it has already initialzed DATE_FORMATS because it has...
DATE_FORMATS = {
# Where 3N is the number of places after the decimal (.)
# If you want microseconds change 3N to 6N
:db => '%Y-%m-%d %H:%M:%S.%3N'
}
end
@ransingh

This comment has been minimized.

Copy link

commented Sep 24, 2014

Nice one

Thanks.

@iamatypeofwalrus

This comment has been minimized.

Copy link
Owner Author

commented Oct 3, 2014

No problem!

@joschka

This comment has been minimized.

Copy link

commented Dec 1, 2014

Thanks, was helpful for me!

Time::DATE_FORMATS.merge!({ db: '%Y-%m-%d %H:%M:%S.%3N' }) also works for me and avoids the warning.

@janraasch

This comment has been minimized.

Copy link

commented Dec 10, 2014

For (my own) reference: MySQL 5.6.4 changelog

@AndrewKL

This comment has been minimized.

Copy link

commented Aug 10, 2015

is "echange_column" supposed to be "change_column"

@dilizarov

This comment has been minimized.

Copy link

commented Nov 12, 2015

This also works great for Postgresql, BUT, I will add that time.rb ended up creating errors when I tried to create or update objects. My error being PG::InvalidDatetimeFormat. Not using time.rb and everything is perfect.

@panupan

This comment has been minimized.

Copy link

commented Jan 14, 2016

Nice. Here's if you want to convert all datetime columns, add indexes and avoid the Time warning:

class MillisecondsDateTime < ActiveRecord::Migration
  def up
    ActiveRecord::Base.connection.tables.each do |table|
      ActiveRecord::Base.connection.columns(table).each do |column|
        if column.type == :datetime
          change_column table, column.name, :datetime, limit: 6
          add_index table, column.name
        end
      end
    end
  end

  def down
    ActiveRecord::Base.connection.tables.each do |table|
      ActiveRecord::Base.connection.columns(table).each do |column|
        if column.type == :datetime
          change_column table, column.name, :datetime
          remove_index table, column.name
        end
      end
    end
  end
end

app/config/initializers/time.rb

Time::DATE_FORMATS[:db] = '%Y-%m-%d %H:%M:%S.%6N'
@maletor

This comment has been minimized.

Copy link

commented Feb 24, 2016

The initializer is not necessary with Rails 4.2.5. It just works with the correct database type DATETIME(6).
Additionally, adding this into an initializer will fix your migrations. All that is left after that is to make the change retroactive.

# Creates DATETIME(6) column types by default which support microseconds.
#
# Without it, regular DATETIME columns are created and Percona does not attach
# microseconds to the default DATETIME types. This has a rich history, refer to
# SQL Server DATETIME2.
ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter::NATIVE_DATABASE_TYPES[:datetime][:limit] = 6
@MarkMurphy

This comment has been minimized.

Copy link

commented May 6, 2016

I've created an updated gist with feedback from this thread.

See https://gist.github.com/MarkMurphy/93adca601b05acffb8b5601df09f66df

  • Fixes a typo or two
  • Migrates all table columns with type :datetime
  • Adds default :limit to AbstractMysqlAdapter::NATIVE_DATABASE_TYPES[:datetime] for future migrations.
  • Fixes "Time::DATE_FORMATS is already initialized"
  • Defaults precision to 6 instead of 3 because may as well.
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.