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.