ActiveRecord storing time without microsecond precision?

Question

I've created tables with the created_at and updated_at columns for activerecord to auto populate those fields. I am using rails 4 and mariadb 5.5 with the mysql2 0.3.13 gem

`created_at` timestamp(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
`updated_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

but when I save an item the precision isn't (6)/microseconds. It is like

+----------------------------+----------------------------+
| created_at                 | updated_at                 |
+----------------------------+----------------------------+
| 2013-10-03 17:31:54.000000 | 2013-10-03 17:31:54.000000 |
| 2013-10-03 17:32:32.000000 | 2013-10-03 17:32:32.000000 |
| 2013-10-03 17:33:29.000000 | 2013-10-03 17:33:29.000000 |
| 2013-10-03 17:35:06.000000 | 2013-10-03 17:35:06.000000 |
| 2013-10-03 18:06:20.000000 | 2013-10-03 18:06:20.000000 |
+----------------------------+----------------------------+

how do I force activerecord to use microsecond precision?

score 1 · Answer 1 · edited May 23 '17 at 10:25

According to this SO post:

How to insert a microsecond-precision datetime into mysql？

MySQL prior to 5.6.4 doesnt support microseconds precision in any temporal type. Thus it looks like you will need to use 2 columns, say the normal created_at as a datetime with an additional created_at_usec which is an int

Something like this. Notice that you'll need an accessor method which composes the two fields into one for easier reading:

module MicrosecondTimestamps
  extend ActiveSupport::Concern
  module ClassMethods
   # none right now
  end

  included do
    before_create :set_created_at
    before_save :set_updated_at

    attr_reader :updated_at, :created_at
  end

  private

  def set_created_at
    if created_at.blank?
      time = Time.now.utc
      self.write_attribute(:created_at, time)
      self.created_at_usec = time.usec
    end
  end

  def set_updated_at
    time = Time.now.utc
    self.write_attribute(:updated_at, time)
    self.updated_at_usec = time.usec
  end

  def created_at
    @created_at ||= begin
      coerced_time = attributes[:created_at].to_i
      Time.at(coerced_time, created_at_usec)
    end
  end

  def updated_at
    @updated_at ||= begin
      coerced_time = attributes[:updated_at].to_i
      Time.at(coerced_time, updated_at_usec)
    end
  end

end

then you can include it in all models that need it:

class Foo < ActiveRecord:Base
  include MicrosecondTimestamps
end

since all my models need this behavior, I've use an abstract model to hold this functionality and for my models to inherit from. But so far, I'm still getting .000000. — Derek, Oct 03 '13 at 18:30
i'm using timestamp, so maybe i need to do some more conversions? — Derek, Oct 03 '13 at 18:33
I'm using mariadb 5.5 which has had microsecond precision since 5.3 — Derek, Oct 03 '13 at 19:43

ActiveRecord storing time without microsecond precision?

1 Answers1