This project is archived and is in readonly mode.

#1562 ✓stale
Suraj N. Kurapati

ActiveRecord postgresql_adapter: Include time zone in result of quoted_date( )

Reported by Suraj N. Kurapati | December 11th, 2008 @ 11:05 PM | in 3.x

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.

Comments and changes to this ticket

  • Suraj N. Kurapati

    Suraj N. Kurapati December 17th, 2008 @ 07:01 PM

    • 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

    Pratik December 21st, 2008 @ 11:02 PM

    • Assigned user set to “Tarmo Tänav”
    • State changed from “new” to “incomplete”

    Patch is missing tests.

    Thanks.

  • Suraj N. Kurapati

    Suraj N. Kurapati December 22nd, 2008 @ 08:40 PM

    Thanks for considering my patch. I will add the tests and attach a new version of this patch this week.

  • Geoff Buesing

    Geoff Buesing December 22nd, 2008 @ 11:07 PM

    Just 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)?

  • Jeremy Kemper

    Jeremy Kemper May 4th, 2010 @ 06:48 PM

    • Milestone changed from 2.x to 3.x
  • MikZ

    MikZ September 10th, 2010 @ 06:15 PM

    • 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 timezone and this http://gist.github.com/574015

  • Suraj N. Kurapati

    Suraj N. Kurapati September 10th, 2010 @ 07:03 PM

    Here is a refactored version of MikZ's and my initial solution: http://gist.github.com/574080

  • MikZ

    MikZ September 15th, 2010 @ 06:56 AM

    • 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_timezone set to something different from UTC (which you need if you want correct values from db), then calling super in quoted_date will convert value to that timezone. But when sending timezone offset, PostgreSQL needs UTC date. Thats why I'm calling value.to_s(:db) and why you can't just call super.

  • Suraj N. Kurapati

    Suraj N. Kurapati September 15th, 2010 @ 07:13 AM

    Thanks for catching (and explaining) that MikZ. I have updated the solution accordingly: http://gist.github.com/574080 Cheers.

  • Espen Antonsen
  • MikZ

    MikZ October 6th, 2010 @ 03:27 PM

    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

    Espen Antonsen December 9th, 2010 @ 04:02 AM

    Any progress with this ticket?

  • rails

    rails March 10th, 2011 @ 12:00 AM

    • 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

    rails March 10th, 2011 @ 12:00 AM

    • State changed from “open” to “stale”

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Referenced by

Pages