Rails 5 handles DateTime with better precision
February 23, 2016
This blog is part of our Rails 5 series.
MySQL 5.6.4 and up has added fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision.
Adding precision to migration
To add precision on datetime column we need to add limit option to it. By
default it is set to 0.
def change
add_column :users, :last_seen_at, :datetime, limit: 6
end
This adds precision(6) to last_seen_at column in users table.
Rails 4.x behavior
Let's look at the some of the examples with different precision values.
The task here is to set end_of_day value to updated_at column.
With precision set to 6
user = User.first
user.updated_at
=> Mon, 18 Jan 2016 10:13:10 UTC +00:00
user.updated_at = user.updated_at.end_of_day
=> Mon, 18 Jan 2016 23:59:59 UTC +00:00
user.save
'UPDATE `users` SET `updated_at` = '2016-01-18 23:59:59.999999' WHERE `users`.`id` = 1'
user.updated_at
=> Mon, 18 Jan 2016 23:59:59 UTC +00:00
user.reload
user.updated_at
=> Mon, 18 Jan 2016 23:59:59 UTC +00:00
Everything looks good here.
But let's look at what happens when precision is set to 0.
With precision set to 0
user = User.first
user.updated_at
=> Mon, 18 Jan 2016 10:13:10 UTC +00:00
user.updated_at = user.updated_at.end_of_day
=> Mon, 18 Jan 2016 23:59:59 UTC +00:00
user.save
'UPDATE `users` SET `updated_at` = '2016-01-18 23:59:59.999999' WHERE `users`.`id` = 1'
user.updated_at
=> Mon, 18 Jan 2016 23:59:59 UTC +00:00
So far everything looks good here too. Now let's see what happens when we reload this object.
user.reload
user.updated_at
=> Tue, 19 Jan 2016 00:00:00 UTC +00:00
As we can clearly see after the reload updated_at value has been rounded off
from 2016-01-18 23:59:59.999999 to 2016-01-19 00:00:00. It might seem like a
small issue but notice that date has changed from 01/18 to 01/19 because of
this rounding.
Improvement in Rails 5
Rails team fixed this issue by removing fractional part if mysql adapter does not support precision.
Here are the two relevant commits to this change.
With precision set to 0
user.updated_at
=> Tue, 19 Jan 2016 00:00:00 UTC +00:00
user.updated_at = user.updated_at.tomorrow.beginning_of_day - 1
=> Tue, 19 Jan 2016 23:59:59 UTC +00:00
user.save
'UPDATE `users` SET `updated_at` = '2016-01-19 23:59:59' WHERE `users`.`id` = 1'
user.reload
user.updated_at
=> Tue, 19 Jan 2016 23:59:59 UTC +00:00
If precision is not set then fractional part gets stripped and date is not changed.
If this blog was helpful, check out our full blog archive.