Time zone of date and time on SQL in Rails

time-zone-of-date-time-on-sql-in-rails.md

これは？

Rails におけるSQL上の日付時刻のタイムゾーンがどのように扱われるかの調査結果

環境

• Ruby 2.7
• Rails 6.1
• MySQL5

1 => SQL上の日時は「UTC」で扱われる

config.time_zone	ENV['TZ']	ActiveRecord::Base.default_timezone
Asia/Tokyo	nil (same as UTC)	:utc

applicatin.rb

    config.time_zone = "Asia/Tokyo"

Result

irb(main):006:0> ActiveRecord::Base.default_timezone
=> :utc
irb(main):007:0> ENV['TZ']
=> nil
irb(main):008:0> Rails.application.config.time_zone
=> "Asia/Tokyo"
irb(main):009:0> t = Time.zone.now
=> Fri, 13 Aug 2021 02:07:36.531457118 JST +09:00
irb(main):011:0> t.zone
=> "JST"
irb(main):012:0> Task.where(created_at: t).to_sql
=> "SELECT \"tasks\".* FROM \"tasks\" WHERE \"tasks\".\"created_at\" = '2021-08-12 17:07:36.531457'"

2 => SQL上の日時は「UTC」で扱われる

config.time_zone	ENV['TZ']	ActiveRecord::Base.default_timezone
Asia/Tokyo	nil (same as UTC)	:local

application.rb

    config.time_zone = "Asia/Tokyo"
+    config.active_record.default_timezone = :local

Result

irb(main):005:0> ActiveRecord::Base.default_timezone
=> :local
irb(main):006:0> ENV['TZ']
=> nil
irb(main):007:0> Rails.application.config.time_zone
=> "Asia/Tokyo"
irb(main):008:0> t = Time.zone.now
=> Fri, 13 Aug 2021 02:20:21.250317844 JST +09:00
irb(main):009:0> t.zone
=> "JST"
irb(main):010:0> Task.where(created_at: t).to_sql
   (0.5ms)  SELECT sqlite_version(*)
=> "SELECT \"tasks\".* FROM \"tasks\" WHERE \"tasks\".\"created_at\" = '2021-08-12 17:20:21.250317'"

3 => SQL上の日時は「Asia/Tokyo（ENV['TZ'])」で扱われる

config.time_zone	ENV['TZ']	ActiveRecord::Base.default_timezone
Asia/Tokyo	Asia/Tokyo	:local

boot.rb

+ ENV['TZ'] = 'Asia/Tokyo'

application.rb

    config.time_zone = "Asia/Tokyo"
    config.active_record.default_timezone = :local

Result

irb(main):004:0> ActiveRecord::Base.default_timezone
=> :local
irb(main):005:0> ENV['TZ']
=> "Asia/Tokyo"
irb(main):006:0> Rails.application.config.time_zone
=> "Asia/Tokyo"
irb(main):007:0> t = Time.zone.now
=> Fri, 13 Aug 2021 02:31:35.462295020 JST +09:00
irb(main):008:0> t.zone
=> "JST"
irb(main):009:0> Task.where(created_at: t).to_sql
=> "SELECT \"tasks\".* FROM \"tasks\" WHERE \"tasks\".\"created_at\" = '2021-08-13 02:31:35.462295'"