CODEWITHSUNDEEP
mysqlruby-on-railsactiverecordtimestamp
Sai
Sai

Reputation: 7229

Making ActiveRecord / Rails use actual mysql TIMESTAMP columns

Rails' :timestamp column type lies; it's actually just an alias for :datetime.

I'm using mysql, and I want to use actual unix-timestamp TIMESTAMP columns.

a) Is there a nice way to set this, other than just making the column using SQL?

b) Will ActiveRecord cope with it properly (e.g. converting to Time when necessary, accepting a unix timestamp Integer as input, etc)? What gotchas should I expect to have to handle, and where?

Why:

  1. Speed. This is for an extremely active table that's aggregating outside data sources that already use unix timestamps. Converting to datetime (or even converting first to a db string, which goes through 2 gsubs) uses up the majority of its import time. I could otherwise be doing just a dirt cheap Integer#to_s call.

  2. Timezones. I don't want 'em. I want it stored timezone-agnostically; dealing with timezones is a pain and is completely irrelevant to my needs except at the very final stage before individual user display. The data itself has no need to know what timezone it was recorded in.

  3. Size. It's a large table. TIMESTAMP is half the size of DATETIME.

Yes, I would still be doing updated_at calculations in code, not mysql. That part isn't a bottleneck.

Why your 'why not' is wrong (preëmptively, to show I'm not asking for noobish reasons :-P):

  1. "But TIMESTAMP auto updates": That's only true by default, and can be easily switched off.
  2. I'm actually not using Rails, just ActiveRecord.
  3. Yes, this is based on actual profiling data; I am not early optimizing. ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter#quote (in Quoting#quoted_date [if passing Time] or Mysql2Adapter#quote_string [if preconverting to_s(:db)]) is actually the most CPU-consuming section of my scraper. I want rid of it.

Upvotes: 11

Views: 3446

Answers (2)

merletta
merletta

Reputation: 444

this works (just added whitespace character to type definition, so :timestamp doesn't override it):

     t.add_column :sometable, :created_at, 'timestamp '

Upvotes: 8

James Dunn
James Dunn

Reputation: 192

I'm pretty noobish, but I'll give it a shot. What if you were to add your own custom column, or overwrite the default ones? You can use custom data types with a string like so:

t.add_column :mysql_timestamp, 'timestamp'

and then somewhere else in your logic

def mysql_timestamp
  Time.now.strftime("%Y-%m-%d %H:%M:%S")
end

Not sure about b). Only one way to find out!

Upvotes: 0

Related Questions