Back to Blog

Rails 5 handles DateTime with better precision

on 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.

1
2def change
3  add_column :users, :last_seen_at, :datetime, limit: 6
4end
5

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

1user = User.first
2user.updated_at
3=> Mon, 18 Jan 2016 10:13:10 UTC +00:00
4
5user.updated_at = user.updated_at.end_of_day
6=> Mon, 18 Jan 2016 23:59:59 UTC +00:00
7
8user.save
9'UPDATE `users` SET `updated_at` = '2016-01-18 23:59:59.999999' WHERE `users`.`id` = 1'
10
11user.updated_at
12=> Mon, 18 Jan 2016 23:59:59 UTC +00:00
13
14user.reload
15user.updated_at
16=> 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

1user = User.first
2user.updated_at
3=> Mon, 18 Jan 2016 10:13:10 UTC +00:00
4
5user.updated_at = user.updated_at.end_of_day
6=> Mon, 18 Jan 2016 23:59:59 UTC +00:00
7
8user.save
9'UPDATE `users` SET `updated_at` = '2016-01-18 23:59:59.999999' WHERE `users`.`id` = 1'
10
11user.updated_at
12=> 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.

1user.reload
2user.updated_at
3=> 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

1user.updated_at
2=> Tue, 19 Jan 2016 00:00:00 UTC +00:00
3
4user.updated_at = user.updated_at.tomorrow.beginning_of_day - 1
5=> Tue, 19 Jan 2016 23:59:59 UTC +00:00
6
7user.save
8'UPDATE `users` SET `updated_at` = '2016-01-19 23:59:59' WHERE `users`.`id` = 1'
9
10user.reload
11
12user.updated_at
13=> 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.


You might also like

If you liked this blog post, check out similar ones from BigBinary