Rails/Postgresql SQL差异与日期

Question

当在psql中运行以下查询时，我得到了7个结果：

SELECT generate_series('2012-10-14', CURRENT_DATE, interval '1 day'); # 7

然而，当我在我的rails应用程序中运行完全相同的查询时，我得到了8个结果：

result = ActiveRecord::Base.connection.execute "SELECT generate_series('2012-10-14', CURRENT_DATE, interval '1 day');"
puts result.count # 8

这似乎与时区有关，但我不知道问题是什么。我的application.rb中有以下内容

config.time_zone = 'Eastern Time (US & Canada)'

这与我在postgresql.conf中的时区设置相同

我不明白为什么我的rails应用程序会为我的结果增加额外的一天。有人能提供一些见解吗？

这似乎只发生在一天快结束的时候(晚上8点以后)，所以这就是我认为这是时区偏移的原因。

Answer 1

您使用的generate_series版本使用的是时间戳，而不是日期。因此，您的'2012-10-14'和current_date将转换为timestamp with time zones，而generate_series将生成一组timestamp with time zones；请比较以下内容：

=> select generate_series('2012-10-14', current_date, '1 day');
    generate_series     
------------------------
 2012-10-14 00:00:00-07
 2012-10-15 00:00:00-07
 2012-10-16 00:00:00-07
 2012-10-17 00:00:00-07
 2012-10-18 00:00:00-07
 2012-10-19 00:00:00-07
 2012-10-20 00:00:00-07
(7 rows)

=> select generate_series('2012-10-14', current_date::timestamp, '1 day');
   generate_series   
---------------------
 2012-10-14 00:00:00
 2012-10-15 00:00:00
 2012-10-16 00:00:00
 2012-10-17 00:00:00
 2012-10-18 00:00:00
 2012-10-19 00:00:00
 2012-10-20 00:00:00
(7 rows)

第一个有时区，第二个没有。

但是，current_date始终转换为应用了数据库会话的时区调整的时间戳。Rails会话将使用UTC与数据库通信，您的psql会话可能正在使用ET。

如果手动指定当前日期并显式使用timestamp：

select generate_series('2012-10-14'::timestamp, '2012-10-20'::timestamp, '1 day')

然后你会在这两种情况下得到相同的七个结果，因为看不到时区会把事情搞得一团糟。

忽略时区的最简单方法是使用整数版本的generate_series，将整数添加到日期会将该整数视为天数：

select '2012-10-14'::date + generate_series(0, 6)

这将使您在没有时区干扰的情况下有相同的七天。您仍然可以使用current_date (没有时区，因为SQL date没有时区)，注意两个日期之间的差异是它们之间的天数(一个整数)：

=> select '2012-10-14'::date + generate_series(0, current_date - '2012-10-14');
  ?column?  
------------
 2012-10-14
 2012-10-15
 2012-10-16
 2012-10-17
 2012-10-18
 2012-10-19
 2012-10-20
(7 rows)

以及来自Rails的：

> pp ActiveRecord::Base.connection.execute("select '2012-10-14'::date + generate_series(0, 6)").to_a
[{"?column?"=>"2012-10-14"},
 {"?column?"=>"2012-10-15"},
 {"?column?"=>"2012-10-16"},
 {"?column?"=>"2012-10-17"},
 {"?column?"=>"2012-10-18"},
 {"?column?"=>"2012-10-19"},
 {"?column?"=>"2012-10-20"}]

顺便说一句，我讨厌时区，讨厌和鄙视它们。