Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ActiveRecord: milliseconds aren't taken into account when using a where clause

I'm working on a project with a rails api and an iOS client, using the updated_at field as the reference to detect modifications on the server that happened after the last pull from the client.

The updated_at datetime has a precision in milliseconds, meaning that

Model.updated_at.to_f

returns something like "1368977381.063427". This is sent to the client formatted as "2013-05-19T15:29:41.063427000Z".

The trouble is, when I get that datetime back from the client, parse it and query with it, the milliseconds are lost.

last_update = DateTime.strptime("2013-05-19T15:29:41.063427000Z", "%Y-%m-%dT%H:%M:%S.%N%z")
Model.where("updated_at > ?", last_update)

is as good as doing

last_update = DateTime.strptime("2013-05-19T15:29:41Z", "%Y-%m-%dT%H:%M:%S%z")
Model.where("updated_at > ?", last_update)

As a result, I always get at least one result when I should get none, because the milliseconds are truncated.

How can I take those into account in my query ?

like image 899
Arnaud Avatar asked May 28 '13 20:05

Arnaud


2 Answers

Try

Model.where("updated_at > ?", last_update.strftime("%Y-%m-%dT%H:%M:%S.%N%z"))

You can also set this format as the standard format for DateTime in databases by setting (i.e. in an initiallizer):

Time::DATE_FORMATS[:db]= '%Y-%m-%dT%H:%M:%S.%N%z'

then your original query works again:

Model.where("updated_at > ?", last_update)

See the Rails API for DateTime.to_s (aka .to_formated_s)

like image 92
Martin M Avatar answered Sep 23 '22 00:09

Martin M


If you're using the ISO8601 standard you can use .iso8601(10). Don't know why but it rounds to seconds by default.

like image 44
Ryan Taylor Avatar answered Sep 26 '22 00:09

Ryan Taylor