Rails / MySQL: How to query for a record by its full 'created_at' timestamp?

user_290580 0
Viewed 164

I want to be able to find a User record by its created_at timestamp (which in this case is: 2020-08-07 11:30:28.5934908).

But the result is this:

User.where(created_at: '2020-08-07 11:30:28.5934908').first
=> nil

The reason that this (existing) User record is not found seems to become evident in the MySQL query that Rails generated:

User Load (0.4ms) SELECT `users`.* FROM `users` WHERE `users`.`created_at` = '2020-08-07 11:30:28.593490' ORDER BY `users`.`id` ASC LIMIT 1

...where for some reason the last digit 8 is dropped from the timestamp 2020-08-07 11:30:28.5934908 used in the MySQL query.

What is the problem here? Does Rails shorten the timestamp in the query? Or does MySQL do this? How do I solve this?

ruby-on-rails mysql
3 Answers

Based on your comments I think that the main problem is in the conversion of the microseconds-precise time to Float. Float (even though in ruby internally a Double) does not have enough accuracy to fully represent all dates / times with microseconds precision, as is documented in the Time class (although they speak about “nanoseconds”, interestingly). Such conversion then only tries to find the nearest possible representation in Float. Rounding the resulting float number back to 6 digits may work but I’m not sure it’s guaranteed to always work…

Suppose that the real time stored in DB is 2020-08-07 11:30:28.593491. As you’ve noticed, this converts to Float imprecisely:

>> Time.parse('2020-08-07 11:30:28.593491').to_f
=> 1596792628.5934908

The guaranteed method would be to use a Rational number instead, i.e. to_r:

>> Time.parse('2020-08-07 11:30:28.593491').to_r
=> (1596792628593491/1000000)

To reconstruct the Time back from the rational number, you can use Time.at:

>> Time.at(Rational(1596792628593491, 1000000)).usec
=> 593491

Note that the microseconds are fully preserved here.

So, storing a created_at time precisely and using it later to search for a record involves using a Rational number variable instead of Float:

>> user_created_at = User.first.created_at.to_r
=> (1596792628593491/1000000)

>> User.where(created_at: Time.at(user_created_at)).first == User.first
=> true

An alternative approach might be to store both the integer seconds since Epoch (User.first.created_at.to_i) and the nanoseconds fraction (User.first.created_at.usec) separately in two variables. They can be the used in Time.at, too, to reconstruct the time back.

As a sidenote, this has also been discussed in a Rails issue with a similar conclusion.

user_15440120

This isn't a direct answer to your question rather a workaround of sorts. You could query the created_at column in some very small time delta like this.

User.where(created_at: '2020-08-07 11:30:28.593490'...'2020-08-07 11:30:28.593491')
user_3087310

As per @BoraMa's comment (thanks!), the MySQL created_at timestamp must have 6 digits only. Thus, the working query would look like this:

User.where(created_at: '2020-08-07 11:30:28.5934908'.round(6)).first
user_2905800
Related