Ruby on Rails如何查询数据库中存储为UTC格式的带时区偏移的datetime数据

核心逻辑：你业务使用的时区为PST（UTC-8），数据库统一存储UTC时间，查询时只需将业务时区的日期范围正确转换为对应的UTC时间范围即可，不需要修改数据库存储规则，也不需要给结束日期加1天。

方法1：Ruby层转换时间（推荐，兼容所有数据库，无SQL兼容问题） #

你之前的转换逻辑问题在于没有将本地时间的起止正确转为UTC时间，正确转换代码如下：

# 可替换为你实际的业务时区，PST对应时区名为"Pacific Time (US & Canada)"
time_zone = ActiveSupport::TimeZone.new("Pacific Time (US & Canada)")
# 将前端传入的日期转成对应时区的起止时间，再转UTC传入ransack查询
@params[:q][:completed_at_gteq] = time_zone.parse(params[:q][:start_date]).beginning_of_day.utc
@params[:q][:completed_at_lteq] = time_zone.parse(params[:q][:end_date]).end_of_day.utc

按你举的示例验证：要查询2021/12/01的订单

• 该日期在PST时区的开始时间是2021-12-01 00:00:00 -0800，转UTC为2021-12-01 08:00:00 UTC
• 该日期在PST时区的结束时间是2021-12-01 23:59:59 -0800，转UTC为2021-12-02 07:59:59 UTC
  你提到的当日17点下单的订单，存储的UTC时间为2021-12-02 01:00:00 UTC，正好在上述UTC时间范围内，会被正常纳入统计。

方法2：数据库层时区转换（适合需要直接按本地日期筛选的场景） #

如果你不想在Ruby层处理时间转换，可以给模型添加ransack自定义方法，查询时将数据库存储的UTC时间转换为业务时区后再匹配日期，以MySQL为例：

# 在app/models/spree/order.rb中添加自定义ransack字段
ransacker :completed_at_local do
  # 第二个参数为数据库存储时区（UTC对应+00:00），第三个参数为业务时区偏移
  Arel.sql("CONVERT_TZ(spree_orders.completed_at, '+00:00', '-08:00')")
end

之后直接传入本地日期即可查询，不需要额外转换时间：

@params[:q][:completed_at_local_gteq] = params[:q][:start_date].to_date
@params[:q][:completed_at_local_lteq] = params[:q][:end_date].to_date

如果使用PostgreSQL，ransacker内的SQL语句改为：
Arel.sql("spree_orders.completed_at AT TIME ZONE 'UTC' AT TIME ZONE 'PST'")

注意事项 #

• 不要直接用to_datetime转换日期字符串，默认会调用系统时区而非业务指定时区，容易出现偏移错误
• 如果已经在Rails配置中设置了全局时区（config.time_zone = 'Pacific Time (US & Canada)'），可以直接用params[:q][:start_date].to_time_in_current_zone代替手动指定时区解析

内容的提问来源于stack exchange，提问作者aRtoo