Rails/Postgresql SQL differences w/ Dates

Question

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.

Answer 1

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.