Rails vs Dates

.gitignore

Gemfile.lock
test.db*

.ruby-version

3.1.2

README.md

notes compiled on or around Thanksgiving, 2023

background

Facts - Assigning the MySQL minimum DATETIME value of 1000-01-1 00:00:00 to an ActiveRecord attribute converts it to a Rails ActiveSupport::TimeWithZone value.

date = DateTime.new(1000, 1, 1, 0, 0, 0, 'UTC')

record.update(happened_at: date)
record.happened_at == date # => false

Comparing that value to the original DateTime value with == returns false.

The MySQL MAX DateTime value (9999-12-31 23:59:59) however, will return true when compared with == to an ActiveSupport::TimeWithZone having the same value.

date = DateTime.new(9999, 12, 31, 23, 59, 59, 'UTC')

record.update(happened_at: date)
record.happened_at == date # => true

I've been nerd-sniped! I need to understand what's going on here, and I'm interested in learning by messing around so I'm just going to see what I see.

system specs

$ uname -a
Linux ... 6.2.0-36-generic #37~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC x86_64 GNU/Linux

$ ruby -v
ruby 3.1.2p20 (2022-04-12 revision 4491bb740a) [x86_64-linux]

$ bundle list | grep activesupport
  * activesupport (7.1.2)

running code from this gist

Clone the gist, install dependencies, run scripts.

$ git clone https://gist.github.com/f97806e1c0fe8e4e1849e5f8412fd339.git
$ cd f97...
$ bundle install
$ ruby date_compare.rb

a:   1000-01-01T00:00:00+00:00 (DateTime)
b:   1000-01-01 00:00:00 UTC (ActiveSupport::TimeWithZone)
eq:  a > b
---
a:   9999-12-31T23:59:59+00:00 (DateTime)
b:   9999-12-31 23:59:59 UTC (ActiveSupport::TimeWithZone)
eq:  a == b
---

etc.

Observations

First, I wrote a script to roll forwards through time to find the first date at which DateTime and ActiveSupport::TimeWithZone line up.

I know 1000-1-1 is strange, so I'll start there and walk forward a day at a time until DateTime == TimeWithZone. Before finishing, print out the last date where they are not equal, and the date one day later where they are.

require 'active_support'
require 'active_support/core_ext/date'

a = DateTime.new 1000, 1, 1, 0, 0, 0, 'UTC'
b = ActiveSupport::TimeWithZone.new a, ActiveSupport::TimeZone['UTC']

while a != b
  a = a + 1
  b = ActiveSupport::TimeWithZone.new a, ActiveSupport::TimeZone['UTC']
end
 
puts " no: #{a - 1}"
puts "yes: #{a}"

This was the output:

$ ruby find_datetime_boundary.rb 

 no: 1582-10-04T00:00:00+00:00
yes: 1582-10-15T00:00:00+00:00

Observation: walking a Ruby DateTime value backward by one day from October 15, 1582 results in a DateTime of October 4, 1582. This is strange.

What happened on October 4, 1582?

October 4 – The Julian calendar is discarded at the end of the day in Italy, Poland, Portugal, and Spain as Pope Gregory XIII implements the Gregorian calendar. In the nations where the calendar is accepted, the day after Monday, October 4, is followed by Friday, October 15.

- Wikipedia, "1582"

Whelp, there you go! DateTime's internal representation must be using the Gregorian calendar, because we can experimentally observe the missing 11 days. Time, on the other hand, must be doing something different, because it can represent those days. Counting backwards from Jan 1, 1970, maybe?

Another way to see the missing days is by creating DateTime objects manually:

$ irb
> require 'date'
> 14.times do |n| 
    puts(DateTime.new(1582, 10, 3 + n)) rescue puts("#{ 3 + n } does not exist") 
  end

1582-10-03T00:00:00+00:00
1582-10-04T00:00:00+00:00
5 does not exist
6 does not exist
7 does not exist
8 does not exist
9 does not exist
10 does not exist
11 does not exist
12 does not exist
13 does not exist
14 does not exist
1582-10-15T00:00:00+00:00
1582-10-16T00:00:00+00:00

Is it just Ruby?

Can this be demonstrated with Ruby on its own? Let's check date increment first:

require 'date'

dt = DateTime.new 1582, 10, 4, 0, 0, 0, '+00:00'
t  = Time.new 1582, 10, 4, 0, 0, 0, '+00:00'
puts "Time:             #{t}"
puts "Time + 1 day:     #{t + 60 * 60 * 24}"
puts
puts "DateTime:         #{dt}"
puts "DateTime + 1 day: #{dt + 1}"

$ ruby time_versus_datetime.rb
Time:             1582-10-04 00:00:00 +0000
Time + 1 day:     1582-10-05 00:00:00 +0000

DateTime:         1582-10-04T00:00:00+00:00
DateTime + 1 day: 1582-10-15T00:00:00+00:00

Alright, we have our shift from Julian to Gregorian. How about comparison?

require 'date'

dt = DateTime.new 1582, 10, 4, 0, 0, 0, '+00:00'
t  = Time.new 1582, 10, 4, 0, 0, 0, '+00:00'
puts format('%s == %s: %s', t, dt, t == dt)

dt = DateTime.new 1582, 10, 15, 0, 0, 0, '+00:00'
t  = Time.new 1582, 10, 15, 0, 0, 0, '+00:00'
puts format('%s == %s: %s', t, dt, t == dt)

$ ruby time_versus_datetime.rb
1582-10-04 00:00:00 +0000 == 1582-10-04T00:00:00+00:00: false
1582-10-15 00:00:00 +0000 == 1582-10-15T00:00:00+00:00: false

This is interesting. Plain-old-Ruby can't successfully compare Time values with DateTime values built from the same yyyy-mm-dd HH:MM:SS inputs.

What if we introduce ActiveSupport and run the same code?

require 'active_support'
require 'active_support/core_ext/date'

dt = DateTime.new 1582, 10, 4, 0, 0, 0, '+00:00'
t  = Time.new 1582, 10, 4, 0, 0, 0, '+00:00'
puts format('%s == %s: %s', t, dt, t == dt)

dt = DateTime.new 1582, 10, 15, 0, 0, 0, '+00:00'
t  = Time.new 1582, 10, 15, 0, 0, 0, '+00:00'
puts format('%s == %s: %s', t, dt, t == dt)

$ ruby time_versus_datetime.rb
1582-10-04 00:00:00 +0000 == 1582-10-04T00:00:00+00:00: false
1582-10-15 00:00:00 +0000 == 1582-10-15T00:00:00+00:00: true

We get our post-Gregorian comparisons back!

Bonus

Last strange thing, the ActiveSupport::TimeWithZone class has an instance method named to_time which returns a plain old Ruby Time object. What happens when we convert our Oct 4, 1582 TimeWithZone using to_time?

> t
=> Mon, 04 Oct 1582 00:00:00.000000000 UTC +00:00
> t.to_time
=> 1582-10-03 19:03:58 -045602

I'm not a time science person, but -045602 doesn't look like any timezone I've ever seen.

Let's ask the object about its time zone.

> t.to_time.zone
=> "LMT"

And now I get to learn about "Local Mean Time".

date_compare.rb

# 
# Compare DateTime values to ActiveSupport::TimeWithZone values created
# from the same input value (yyyy-mm-dd HH:MM:SS)
# 
# We expect two dates to be the same, but find that 1000-1-1 and
# 9999-12-31 behave differently.
#
require 'active_support'
require 'active_support/core_ext'

UTC = ActiveSupport::TimeZone["UTC"]

%w(1000-01-01T00:00:00Z 9999-12-31T23:59:59Z).each do |time_string|
  a = DateTime.parse(time_string)
  b = ActiveSupport::TimeWithZone.new a, UTC
  puts <<~OUT
    a:   #{a} (#{a.class})
    b:   #{b} (#{b.class})
    eq:  #{a == b ? 'a == b' : (a < b ? 'a < b' : 'a > b')}
    ---
  OUT
end

find_datetime_boundary.rb

require 'active_support'
require 'active_support/core_ext/date'

a = DateTime.new 1000, 1, 1, 0, 0, 0, 'UTC'
b = ActiveSupport::TimeWithZone.new a, ActiveSupport::TimeZone['UTC']

while a != b
  a = a + 1
  b = ActiveSupport::TimeWithZone.new a, ActiveSupport::TimeZone['UTC']
end
 
puts " no: #{a - 1}"
puts "yes: #{a}"

Gemfile

source 'https://rubygems.org'

gem 'sqlite3'
gem 'rails', '~> 7'

rails_date_attribute.rb

require 'active_record'
require 'active_support/core_ext'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

class CreateEvents < ActiveRecord::Migration[7.0]
  def change
    create_table :events, force: true do |t|
      t.datetime :min_at
      t.datetime :max_at
    end
  end
end

# Create the table
CreateEvents.migrate(:up)

class Event < ActiveRecord::Base
end

# create a new model
min_date = DateTime.new(1000, 1, 1, 0, 0, 0, 'UTC')
max_date = DateTime.new(9999, 12, 31, 23, 59, 59, 'UTC')

record = Event.create min_at: min_date, max_at: max_date
record.reload

puts '--- default min_at ---'
puts "t:  #{ record.min_at } (#{ record.min_at.class })"
puts "dt: #{ min_date } (#{ min_date.class })"
puts "eq: #{ record.min_at == min_date }"

puts '--- default max_at ---'
puts "t:  #{ record.max_at } (#{ record.max_at.class })"
puts "dt: #{ max_date } (#{ max_date.class })"
puts "eq: #{ record.max_at == max_date }"

puts '--- min_at.to_time ---'
puts "t:  #{ record.min_at.to_time }"
puts "dt: #{ min_date.to_time }"
puts "eq: #{ record.min_at.to_time === min_date.to_time }"

time_versus_datetime.rb

require 'active_support'
require 'active_support/core_ext/date'

dt = DateTime.new 1582, 10, 4, 0, 0, 0, '+00:00'
t  = Time.new 1582, 10, 4, 0, 0, 0, '+00:00'
puts format('%s == %s: %s', t, dt, t == dt)

dt = DateTime.new 1582, 10, 15, 0, 0, 0, '+00:00'
t  = Time.new 1582, 10, 15, 0, 0, 0, '+00:00'
puts format('%s == %s: %s', t, dt, t == dt)