ruby-on-rails-3 - Arel 和 pgAdmin 从具有时间戳条件的同一查询返回不同的数据

Question

这是我在使用 PostgreSQL 9.1 和 Rails 3.0.7 时遇到的最令人费解的问题。我在同一个 Ubuntu 10.04 vbox 客户机上运行 Postgres 和 Rails 3:

"PostgreSQL 9.1.3 on i686-pc-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit"

可能应用程序需要基于 created_at 时间戳运行一个简单的查询，该时间戳落在基于服务器时区的一天。就我而言，东部时间。我已经尝试了“with time zone”和“at time zone”的不同变体，并且在 pgAdmin 中运行良好，但在 Rails 网站中却不行。

这是带有 ARel 查询的类方法。

def self.transactions2(business_id, begin_date, end_date )
trans = LogVouchers.select( %{
      created_at
  } ).
where( %{
     created_at >= ( TIMESTAMP WITH TIME ZONE ? at time zone 'utc')::timestamp
    and created_at < (TIMESTAMP WITH TIME ZONE ? at time zone 'utc')::timestamp
  },
     begin_date,
    end_date  )

end

Controller 代码如下:

  def voucher_transactions
    default_date = Time.zone.now.beginning_of_day

    @end_date = params[:end_date].blank? ? \
      Date.new(default_date.year, default_date.month,  default_date.day )  : \
        Date.new(params[:end_date][:year].to_i, params[:end_date][:month].to_i, \
        params[:end_date][:day].to_i)

    @begin_date = params[:begin_date].blank? ? \
      Date.new(default_date.year, default_date.month,  default_date.day )   : \
        @begin_date = Date.new(params[:begin_date][:year].to_i, \
        params[:begin_date][:month].to_i, params[:begin_date][:day].to_i)

    @data2  = LogVouchers.transactions2(business_id, @begin_date, @end_date + 1.day)
    ...
    end

这是 Rails 日志文件中的实际 SQL 代码和调试检查:

      LogVouchers Load (0.7ms)  SELECT 
 created_at
 FROM "log_vouchers" WHERE (
 created_at >= ( TIMESTAMP WITH TIME ZONE '2012-04-28' at time zone 'utc')::timestamp
 and created_at < (TIMESTAMP WITH TIME ZONE '2012-04-29' at time zone 'utc')::timestamp
 )

  ### 2012-05-01 21:18:04 -0400  voucher_transactions() @data2 =[
  #<LogVouchers created_at: "2012-04-28 03:14:29">
, #<LogVouchers created_at: "2012-04-28 03:15:24">
, #<LogVouchers created_at: "2012-04-28 03:18:19">
, #<LogVouchers created_at: "2012-04-28 03:38:35">
, #<LogVouchers created_at: "2012-04-28 03:58:08">
, #<LogVouchers created_at: "2012-04-28 15:44:46">
, #<LogVouchers created_at: "2012-04-28 17:12:20">
, #<LogVouchers created_at: "2012-04-28 18:46:45">
, #<LogVouchers created_at: "2012-04-28 18:55:47">
, #<LogVouchers created_at: "2012-04-28 18:57:52">
, #<LogVouchers created_at: "2012-04-28 19:02:15">
, #<LogVouchers created_at: "2012-04-28 19:02:50">
, #<LogVouchers created_at: "2012-04-28 19:46:36">
, #<LogVouchers created_at: "2012-04-28 19:47:17">
, #<LogVouchers created_at: "2012-04-28 19:50:22">
, #<LogVouchers created_at: "2012-04-28 19:50:56">]

但是，如果我直接在 pgAdmin 的查询窗口中运行 SQL 代码，我得到了正确的结果:

"2012-04-28 15:44:46.641305"
"2012-04-28 17:12:20.615641"
"2012-04-28 18:46:45.277561"
"2012-04-28 18:55:47.40109"
"2012-04-28 18:57:52.616501"
"2012-04-28 19:02:15.542964"
"2012-04-28 19:02:50.888847"
"2012-04-28 19:46:36.16556"
"2012-04-28 19:47:17.084047"
"2012-04-28 19:50:22.672805"
"2012-04-28 19:50:56.376571"

请注意，UTC 时间“2012-04-28 03:14:29”附近的 created_at 的那 5 条记录不在正确的结果集中。

现在最疑惑的问题是:
Rails 如何将相同的 SQL 语句发送到 Postgres 数据库并得到不同的结果？

我显然在这里遗漏了一些东西。有高手可以帮忙吗？

补充说明 2012-5-2

背景:Rails 3.0 默认在所有数据库表中生成 created_at 列作为 Postgres 中的“没有时区的时间戳”。我需要根据服务器时区“美国/纽约”运行每日报告。由于涉及的表较多，我使用ARel select方法连接多个表，并传入了begin_date和end_date时间戳。我很难返回正确的结果集。它应该返回带有 UTC 时间戳的记录:

2012-04-28 04:00:00    to   2012-04-29 04:00:00    

但它返回带有 UTC 时间戳的记录:

2012-04-28 00:00:00    to   2012-04-29 00:00:00    

我能想到的是改变类方法:

def self.transactions2(business_id, begin_date, end_date )

st_tz_offset      =   Time.zone.now.formatted_offset(true)
st_begin_date_tz  =   "#{begin_date} 00:00:00#{st_tz_offset}"
st_end_date_tz    =   "#{end_date} 00:00:00#{st_tz_offset}"

trans = LogVouchers.select( %{
      created_at
  } ).
where( %{
     created_at >= ( TIMESTAMP WITH TIME ZONE ? at time zone 'utc')
    and created_at < (TIMESTAMP WITH TIME ZONE ? at time zone 'utc')
  },
     st_begin_date_tz  ,
    st_end_date_tz  )

end

事实上，这告诉 Postgres 在比较之前将 timestamp with time zone 转换为 timestamp without time zone。这对我有用。我知道这太复杂了。我非常感谢有人指出实现此目标的更好方法。

补充说明2

• 如果可能，我会避免更改 Ubuntu/Linux 服务器和 Postgres 服务器的默认设置，因为它往往会导致其他副作用。
• 将来，这些每日截止时间戳将基于current_user 的TimeZone 设置，而不是服务器的TimeZone。因为同一网络应用程序的用户可能跨越多个时区，每日报告必须对各自的登录用户有意义。

Answer 1

您的表情取决于本地时间设置。这:

SET  timezone = 'EST';
SELECT TIMESTAMP WITH TIME ZONE '2012-04-29' AT TIME ZONE 'UTC';

返回一个与此不同的时间戳:

SET  timezone = '-2';
SELECT TIMESTAMP WITH TIME ZONE '2012-04-29' AT TIME ZONE 'UTC';

显然，您在 pgAdmin 连接中的时间设置与在 ARel 连接中的时间设置不同。找出:

SHOW timezone;

timezone 在 postgresql.conf 中定义，但可以随时设置为不同的值。您可以使用以下方法将其重置为默认值:

RESET timezone;

我解释了时间戳和 time zones in more detail in this recent answer 的 PostgreSQL 处理.
更多关于 time zones in the manual .

---

如果您想要“UTC 午夜”，请改用此表达式:

SELECT TIMESTAMP '2012-04-29' AT TIME ZONE 'UTC';

或者简单地说:

SELECT '2012-04-29 0:0 +0'::timestamptz;

无论 timezone 设置如何，这都会返回相同的内部值。不过，它将根据本地时区显示。因此，使用 EST，您将看到:

2012-04-28 19:00:00-05

---

无时区

根据您的附加信息，您的表包含类型为timestamp without time zone 的数据 - 或者只是timestamp，默认为without time zone。这些时间戳隐含地基于 EST。

如果你想检索一天的数据，从午夜开始，在午夜结束，独立于你当前所在的时区，不要使用数据类型timestamp with time zone(或 timestamptz)。只需使用 timestamp 一切都很正常。服务器应该得到:

WHERE created_at >= timestamp '2012-04-28 0:0'
AND   created_at <  timestamp '2012-04-29 0:0'

或者:

WHERE created_at >= '2012-04-28 0:0'::timestamp
AND   created_at <  '2012-04-29 0:0'::timestamp

甚至只是:

WHERE created_at >= '2012-04-28 0:0'
AND   created_at <  '2012-04-29 0:0'

如果您根据您当前的时区想要不同的数据片段，那么服务器应该得到这个:

WHERE created_at >= '2012-04-28 0:0'::timestamptz AT TIME ZONE 'EST'
AND   created_at <  '2012-04-29 0:0'::timestamptz AT TIME ZONE 'EST'

术语 '2012-04-28 0:0'::timestamptz AT TIME ZONE 'EST' 计算本地午夜时纽约(无时区)的时间。

或者您可以相应地计算您应用中的时间戳，并使用上述不带时区的查询。