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.

mu is too short · Accepted Answer · 2012-10-22 04:52:49Z

9

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.

edited Oct 22, 2012 at 4:52

answered Oct 21, 2012 at 2:05

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Erwin Brandstetter Over a year ago

+1 Excellent explanation. BTW, I reserve my hatred for the moronic concept of DST (daylight saving time) that neither saves any daylight nor makes any sense. Just wastes a lot of time all over the world by causing confusion. DST should be removed from existence and never be mentioned again.

mu is too short Over a year ago

@ErwinBrandstetter: I can't talk about DST in a public forum that discourages swearing, I just can't do it.

Kyle Decot Over a year ago

Thanks for the in depth explanation, though it just makes me hate timezones that much more ;)

Collectives™ on Stack Overflow

Rails/Postgresql SQL differences w/ Dates

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related