Rails / Postgresql SQL差异w / Dates [英] Rails/Postgresql SQL differences w/ Dates

问题描述

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

  SELECT generate_series（'2012-10-14' CURRENT_DATE，间隔'1天'）; ＃7 
  

但是，当我在rails应用程序中运行完全相同的查询时，我得到8个结果： / p>

  result = ActiveRecord :: Base.connection.executeSELECT generate_series（'2012-10-14'，CURRENT_DATE，interval'1天'）; 
 puts result.count＃8 
  

看起来像这样做有w / timezones但我不知道这个问题是什么。我的application.rb中有以下内容

  config.time_zone ='东部时间（美国和加拿大）'
  

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

我很困惑，为什么我的rails应用程序为我的结果添加了一个额外的一天。任何人都可以提供一些洞察力？

这只是一天结束（8点以后）才发生，所以这是什么让我觉得这是有时间的区域偏移

解决方案

generate_series 正在使用时间戳而不是日期。因此，您的'2012-10-14'和 current_date 正在转换为时间戳与时间区域 s和 generate_series 正在生成一组时间戳与时区 s;比较这些：

  =>选择generate_series（'2012-10-14'，current_date，'1天'）; 
 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行）
 
 =>选择generate_series（'2012-10-14'，current_date :: timestamp，'1天'）; 
 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行）
  

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

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

如果您手动指定当前日期并明确使用 timestamp s：

  select generate_series （'2012-10-14'::时间戳，'2012-10-20'::时间戳，'1天'）
  

那么你会得到同样的七个结果，因为没有时区可以让一团糟的东西。

最简单的方法要忽略时区是使用整数版本的 generate_series ，并且向日期添加整数的事实将整数视为几天：

  select'2012-10-14':: date + generate_series（0，6）
  

这将给您相同的七天没有时区干扰。您仍然可以使用 current_date （由于SQL日期没有时区，因此没有时区），注意到两个日期之间的差异是它们之间的天数（一个整数）：

  =>选择'2012-10-14':: date + generate_series（0，current_date  - '2012-10-14'）; 
？列？ 
 ------------ 
 2012-10-14 
 2012-10-15 
 2012-10-16 
 2012-10 -17 
 2012-10-18 
 2012-10-19 
 2012-10-20 
（7行）
  

和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}] 
  / pre> 
 
 
 BTW，我讨厌时区，讨厌和鄙视他们。
 
When running the following query in psql I get back 7 results:
SELECT generate_series('2012-10-14', CURRENT_DATE, interval '1 day'); # 7
However when I run the exact same query in my rails application, I get 8 results:
result = ActiveRecord::Base.connection.execute "SELECT generate_series('2012-10-14', CURRENT_DATE, interval '1 day');"
puts result.count # 8
It seems like this has something to do w/ timezones but I don't know what the problem could be. I have the following in my application.rb
config.time_zone = 'Eastern Time (US & Canada)'
This is the same time zone setting that I have in my postgresql.conf

I'm confused at to why my rails application is adding an additional day to my results. Can anyone provide some insight?

This only seems to happen towards the end of the day (after 8PM) so this is what makes me think it's something w/ time zone offsets.
解决方案
The version of generate_series that you're using is working with timestamps, not dates. So your '2012-10-14' and current_date are getting converted to timestamp with time zones and generate_series is producing a set of timestamp with time zones; compare these:
=> 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)
The first one has time zones, the second one doesn't.

But, the current_date always gets converted to a timestamp with the database session's time zone adjustment applied. The Rails session will talk to the database in UTC, your psql session is probably using ET.

If you manually specify the current date and explicitly work with timestamps:
select generate_series('2012-10-14'::timestamp, '2012-10-20'::timestamp, '1 day')
then you'll get the same seven results in both because there's no time zone in sight to make a mess of things.

The easiest way to ignore time zones is to use the integer version of generate_series and the fact that adding an integer to a date treats the integer as a number of days:
select '2012-10-14'::date + generate_series(0, 6)
That will give you the same seven days without time zone interference. You can still use the current_date (which has no time zone since SQL dates don't have time zones) by noting that the difference between two dates is the number of days between them (an integer):
=> 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)
and from 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"}]
BTW, I hate time zones, hate and despise them.

                        
这篇关于Rails / Postgresql SQL差异w / Dates的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！