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"

May 应用程序需要根据服务器的时区created_at时间戳运行简单的查询。就我而言，东部时间。我尝试了"时区"和"时区"的不同变体，并使其在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

下面是控制器代码：

  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 时created_at"2012-04-28 03：14：29"的 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。因为同一 Web 应用程序的用户可以跨越多个时区，并且每日报告必须对各自的登录用户有意义。

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;

我在最近的回答中更详细地解释了PostgreSQL对时间戳和时区的处理。
有关时区的更多信息，请参阅手册。

---

如果要使用"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'计算当地午夜时分纽约的时间(无时区(。

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