mysql - Rails和MySQL不同时区影响查询

Question

MySQL中的约会记录

mysql> select * from appointment where id = 2 \G
*************************** 1. row ***************************
                                 id: 2
                       care_unit_id: NULL
           provider_organization_id: 2
                         patient_id: 12
                       clinician_id: 10
                          public_id: NULL
                appointment_type_id: 3
                    scheduled_by_id: 3
              appointment_status_id: 1
appointment_status_change_reason_id: NULL
               scheduled_start_time: 2014-07-23 05:48:00
                      check_in_time: NULL
                     check_out_time: NULL
                 dictation_recorded: NULL
              dictation_transcribed: NULL
                 documentation_time: NULL
                           comments: 
                      documentation: NULL
                         created_at: 2014-07-23 02:48:54
                         updated_at: 2014-07-23 05:48:54
                       lock_version: 0
                      reminder_sent: 0
1 row in set (0.00 sec)

Rails 中的相同记录

1.9.1 :058 > Appointment.find(2)
  Appointment Load (45.3ms)  SELECT `appointment`.* FROM `appointment` WHERE `appointment`.`id` = 2 LIMIT 1
 => #<Appointment id: 2, care_unit_id: nil, provider_organization_id: 2, patient_id: 12, clinician_id: 10, public_id: nil, appointment_type_id: 3, scheduled_by_id: 3, appointment_status_id: 1, appointment_status_change_reason_id: nil, scheduled_start_time: "2014-07-23 05:48:00", check_in_time: nil, check_out_time: nil, dictation_recorded: nil, dictation_transcribed: nil, documentation_time: nil, comments: "", documentation: nil, created_at: "2014-07-23 02:48:54", updated_at: "2014-07-23 05:48:54", lock_version: 0, reminder_sent: false> 
1.9.1 :059 > 
1.9.1 :060 >   Appointment.find(2).created_at
  Appointment Load (0.4ms)  SELECT `appointment`.* FROM `appointment` WHERE `appointment`.`id` = 2 LIMIT 1
 => Tue, 22 Jul 2014 22:48:54 EDT -04:00 
1.9.1 :061 > 

如您所见，created_at 相差一天。

现在的问题是我无法使用created_at按预期形成查询

1.9.1 :073 >   Appointment.where("created_at >= ? ",Appointment.find(2).created_at.beginning_of_day)
  Appointment Load (0.6ms)  SELECT `appointment`.* FROM `appointment` WHERE `appointment`.`id` = 2 LIMIT 1
  Appointment Load (0.3ms)  SELECT `appointment`.* FROM `appointment` WHERE (created_at = '2014-07-22 04:00:00' )
 => [] 
1.9.1 :074 > 

正如您所看到的，上面的查询应该返回一条记录，但 created_at 值折叠了它。

我想知道 Rails 用户如何处理这种情况。谢谢

Answer 1

数据库中存储的时间始终采用 UTC 格式。

Rails 应用程序读取的时间将根据您的应用程序时区设置而有所不同 - 而您的时区是 UTC -4，因此您的 Rails 应用程序读取它们的时间比数据库存储它们的时间早四个小时。这是完全正常的。时间是相同的，只是时区不同。

这实际上会给您带来任何问题吗？