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?

mu is too short · Answer 1 · 2013-01-01T03:43:48.423

24

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.

edited Jan 01 '13 at 03:43

answered Jan 01 '13 at 02:14

mu is too short

426,620
70
833
800

1

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 Jan 01 '13 at 02:48
That is: The problem isn't in created_at, but is in other fields. – SRobertJames Jan 01 '13 at 02:54
1

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 Jan 01 '13 at 03:07
1

And what happens if you convert the string to a `DateTime` or `ActiveSupport::TimeWithZone` or whatever yourself rather than letting AR do it? – mu is too short Jan 01 '13 at 03:08
1

Do not forget the timezone with `%z`. So the code will look like this: `.strftime('%Y-%m-%d %H:%M:%S.%N %z')` – Fabian Feb 09 '16 at 12:44
@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. – mu is too short Feb 09 '16 at 19:19

score 9 · Accepted Answer · answered Jan 01 '13 at 10:34

9

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.

answered Jan 01 '13 at 10:34

SRobertJames

8,210
14
60
107

1

Confirmed, saving it as a string instead of a datetime preserves the fractional seconds. – Dan Sandberg Dec 11 '13 at 22:56
9

Note that `DateTime.now.to_s` doesn't return the fractional seconds. I used `DateTime.now.iso8601(6)` instead. – user1003545 Aug 19 '14 at 16:17
2

This issue is crazy, I've just lost two hours on this. Thx for the answer. – sidney Mar 15 '16 at 16:31
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"` – Dorian Mar 28 '17 at 23:51

Joseph Lord · Answer 3 · 2013-11-15T14:39:26.567

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.

score 1 · Answer 4 · answered Jan 01 '13 at 02:03

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.

Millisecond resolution of DateTime in Ruby

4 Answers4

Linked