CODEWITHSUNDEEP
ruby-on-railspostgresqlparsingtimecomparison
JapanRob
JapanRob

Reputation: 384

Comparing times in postgres and Rails

Very simply put, I have a query:

@current_user.items.where('updated_at > ? AND updated_at < ?', last_sync_time_from_client, current_time)

My problem is, I think the time being compared in the database and the last_sync_time_from_client are being compared on a float basis. This always results in the updated_at > last_sync_time_from_client being true even when the time is identical to the second.

I.e.

(db item)updated_at.to_f # 1541246811.022979
last_sync_time.to_f # 1541246811.0

This means that the times, which are the same down to the second, will return oddities. Is there a way to fix this, or should I simply add a second to the last_sync_time_from_client to account for Rails being strange here?

Upvotes: 1

Views: 745

Answers (1)

Masa Sakano
Masa Sakano

Reputation: 2267

Here are three solutions. Solution 1 is the best (non-destructive). Solutions 2 and 3 affect the stored values in database directly, and so choose it at your own risk.

Solution 1

Use 

date_trunc('second', updated_at);

instead of updated_at. See the answer to "Discard millisecond part from timestamp" for detail.

Solution 2

Force Rails always to update the timestamp with the precision of a second, truncating the sub-second part to zero.

See an answer to "Is there a way to change Rails default timestamps to Y-m-d H:i:s (instead of Y-m-d H:i:s.u) or have laravel ignore decimal portion of Y-m-d H:i:s.u?"

Solution 3

Make the precision of the updated_at column in the database a second, as opposed to the default sub-second (like a milli-second).

See the answers to "how to change rails migration t.timestamps to use timestamp(0) without timezone in postgres" for how to do it with Rails migration.

Upvotes: 2

Related Questions