Comparing times in postgres and Rails

Question

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?

Nothing is strange here. Seconds are not the smallest unit of time. You can truncate the date to the second if your logic dictates that, but I don't see why you should. Don't you want to return the items that were updated 1 millisecond after the last_sync_time_from_client? Do you also expect the updated_at to be in the future? If not, then ensuring that it is less than current_time is not necessary — AbM
– AbM, Commented Nov 3, 2018 at 13:46

Masa Sakano · Accepted Answer · 2018-11-04 08:26:22Z

2

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.

answered Nov 4, 2018 at 8:26

Masa Sakano

2,30925 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JapanRob Over a year ago

Thank you. I realized that there was a problem with my own code - I should have been keeping the milliseconds in the first place, but I accepted this answer because it's really informative.

Collectives™ on Stack Overflow

Comparing times in postgres and Rails

1 Answer 1

Solution 1

Solution 2

Solution 3

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Solution 1

Solution 2

Solution 3

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related