This project is archived and is in readonly mode.
ActiveRecord postgresql_adapter: Include time zone in result of quoted_date( )
When the time zone is not included in a quoted date (which was the case before this patch), we make the assumption that the database server resides in (or is configured as if it resides in) the same time zone as the application which is using the ActiveRecord library to perform SQL queries.
This patch removes the above assumption by explicitly including the time zone in quoted dates, so that ActiveRecord generates SQL queries that work regardless of whether your application happens to be running in the same time zone as your Postgres database server.
See http://www.postgresql.org/docs/8... for reference.
-
Suraj N. Kurapati- Tag changed from patch to activerecord, patch, postgresql, quoting
- Title changed from postgresql_adapter: Include time zone in result of quoted_date( ) to ActiveRecord postgresql_adapter: Include time zone in result of quoted_date( )
-
Pratik- Assigned user set to Tarmo Tänav
- State changed from new to incomplete
Patch is missing tests.
Thanks.
-
Suraj N. Kurapati
Thanks for considering my patch. I will add the tests and attach a new version of this patch this week.
-
Geoff BuesingJust to clarify, this is only an issue with Postgres "timestamp with time zone" columns, correct? There shouldn't be an issue with the "timestamp without time zone" type, which is the type generated by ActiveRecord schema migrations.
WIth this proposed solution, we'd be sending the zone for every Postgres datetime column, whether or not it needs it (if you're following Rails conventions, you'll never need it.) I'm assuming Postgres will ignore the zone when it doesn't need it, but I'm not sure.
Your solution sends #zone, which returns a three-letter abbreviation -- this is ambiguous -- "CST", for example, can represent any number of zones, for example: US Central Standard Time (which has a utc offset of -6 or -5 hours), Chinese Standard Time (which has a utc offset of +8), etc. Better to send the unambiguous #formatted_offset value, e.g. "-06:00", and not rely on Postgres' interpretation of these three-letter abbreviations.
Finally, how will Rails handle data returned from these columns? Will these columns be converted to ActiveRecord::Base.default_timezone, either in ActiveRecord code, or in Postgres (via a per-connection time zone setting, or something)?
-
-
MikZ- Importance changed from to
I've run into this issue yesterday.
Proper way of solving this is to use utc time and format with time zone offset for column with time zone and whatever to column without time zone.
Saving timestamp to column without time zone is also broken (when I have CEST timezone, adapter saves timestamp with timezone offset and Rails adds this offset again after reloading values). Postgres silently ignores time zone settings for columns without time zone.
I've fixed all these problems by using
timestamp with timezoneand this http://gist.github.com/574015 -
Suraj N. Kurapati
Here is a refactored version of MikZ's and my initial solution: http://gist.github.com/574080
-
MikZ
- Tag changed from activerecord, patch, postgresql, quoting to activerecord, patch, postgresql, quoting, timewithzone, timezone, utc
I think there is one major problem with your solution. If its
config.active_record.default_timezoneset to something different from UTC (which you need if you want correct values from db), then callingsuperinquoted_datewill convert value to that timezone. But when sending timezone offset, PostgreSQL needs UTC date. Thats why I'm callingvalue.to_s(:db)and why you can't just callsuper. -
Suraj N. Kurapati
Thanks for catching (and explaining) that MikZ. I have updated the solution accordingly: http://gist.github.com/574080 Cheers.
-
Espen AntonsenThe patch from MikZ fixes this bug for me. Thanks!
-
MikZ
I was wrong.
Postgres expects local date + time zone of date.If you pass time without timezone to postgress and have set TIME ZONE its treated like with timezone in quoted date.
Problem is somewhere else.
ActiveRecord expects dates from DB in UTC (even if you set config.active_record.default_timezone) , but Postgres passes them in TIME ZONE. -
Espen Antonsen
Any progress with this ticket?
-
rails- State changed from incomplete to open
This issue has been automatically marked as stale because it has not been commented on for at least three months.
The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.
Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.
-
rails
- State changed from open to stale