Millisecond resolution of DateTime in Ruby

Question

I have a string like 2012-01-01T01:02:03.456 that I am storing in a Postgres database TIMESTAMP using ActiveRecord.

Unfortunately, Ruby seems to chop off the milliseconds:

ruby-1.9.3-rc1 :078 > '2012-12-31T01:01:01.232323+3'.to_datetime
 => Mon, 31 Dec 2012 01:01:01 +0300 

Postgrs supports microsecond resolution. How can I get my timestamp to be saved accordingly? I need at least millisecond resolution.

(PS Yes I could hack in a milliseconds integer column in postgres; that kind of defeats the whole purpose of ActiveRecord.)

UPDATE:
The very helpful responses showed that Ruby's DateTime is not chopping off milliseconds; using #to_f shows it. But, doing:

m.happened_at = '2012-01-01T00:00:00.32323'.to_datetime
m.save!
m.reload
m.happened_at.to_f

Does drop the milliseconds.

Now, the interesting thing is that created_at does show milliseconds, both in Rails and Postgres. But other timestamps fields (like happened_at above) don't. (Perhaps Rails uses a NOW() function for created_at as opposed to passing in a DateTime).

Which leads to my ultimate question:
How can I get ActiveRecord to preserve millisecond resolution on timestamp fields?

Solution 1

Changing m.happened_at = '2012-01-01T00:00:00.32323'.to_datetime in the code above to m.happened_at = '2012-01-01T00:00:00.32323' solves the problem, though I have no idea why.

Solution 2

ActiveRecord should preserve the full precision from the database, you're just not looking at it properly. Use strftime and the %N format to see the fractional seconds. For example, psql says this:

=> select created_at from models where id = 1;
         created_at         
----------------------------
 2012-02-07 07:36:20.949641
(1 row)

and ActiveRecord says this:

> Model.find(1).created_at.strftime('%Y-%m-%d %H:%M:%S.%N')
 => "2012-02-07 07:36:20.949641000" 

So everything is there, you just need to know how to see it.

Also note that ActiveRecord will probably give you ActiveSupport::TimeWithZone objects rather than DateTime objects but DateTime preserves everything too:

> '2012-12-31T01:01:01.232323+3'.to_datetime.strftime('%Y-%m-%d %H:%M:%S.%N')
 => "2012-12-31 01:01:01.232323000" 

---

Have a look at connection_adapters/column.rb in the ActiveRecord source and check what the string_to_time method does. Your string would go down the fallback_string_to_time path and that preserves fractional seconds as near as I can tell. Something strange could be going on elsewhere, I wouldn't be surprised given the strange things I've seen in the Rails source, especially the database side of things. I'd try converting the strings to objects by hand so that ActiveRecord will keeps its hands off them.

Solution 3

I ended up here when I was suffering from using the RVM provided binary Ruby 2.0.0-p247 on OS X (Mavericks) which was causing rounding to whole values of seconds when retrieving times from Postgres. Rebuilding Ruby myself (rvm reinstall 2.0.0 --disable-binary) solved the issue for me.

See https://github.com/wayneeseguin/rvm/issues/2189 which I found via https://github.com/rails/rails/issues/12422.

I recognise that this is not THE answer to this issue but I hope this note might help someone struggling with it.

Solution 4

to_datetime does not destroy millisecond resolution of data - it's simply hidden because DateTime#to_s doesn't display it.

[1] pry(main)> '2012-12-31T01:01:01.232323+3'.to_datetime
=> Mon, 31 Dec 2012 01:01:01 +0300
[2] pry(main)> '2012-12-31T01:01:01.232323+3'.to_datetime.to_f
=> 1356904861.232323

That said, I suspect that ActiveRecord is mistakenly hiding that information when persisting the data; remember that it is database-agnostic, so it takes approaches that are guaranteed to work across all of its database targets. While Postgres supposed microsecond information in timestamps, MySQL does not, so I suspect AR selects for the lowest common denominator. I couldn't be sure without getting into the guts of AR. You may need a Postgres-specific monkeypatch to enable this behavior.

View more solutions

Comments

[SRobertJames]

Interesting, although created_at does show ms resolution, when I have a datetime field in my model, save it, and reload it, it looses ms resolution.

[SRobertJames]

That is: The problem isn't in created_at, but is in other fields.

[mu is too short]

I only used created_at as that's what I had handy. Where are you losing precision? Before it goes into the database? In the database? After you pull it out?

[mu is too short]

And what happens if you convert the string to a DateTime or ActiveSupport::TimeWithZone or whatever yourself rather than letting AR do it?

[Dan Sandberg]

Confirmed, saving it as a string instead of a datetime preserves the fractional seconds.

[user1003545]

Note that DateTime.now.to_s doesn't return the fractional seconds. I used DateTime.now.iso8601(6) instead.

[Fabian]

Do not forget the timezone with %z. So the code will look like this: .strftime('%Y-%m-%d %H:%M:%S.%N %z')

[mu is too short]

@Fabian But this is Rails so the column will be a plain timestamp (rather than timestamp with time zone) and the time zone will be UTC.

[sidney]

This issue is crazy, I've just lost two hours on this. Thx for the answer.

[Dorian]

Very likely liked to .to_s(:db) formatter, e.g. .created_at.to_s(:db) # "2017-03-25 00:39:49", also: DateTime.now.to_s(:db) # => "2017-03-28 16:54:01"