How to map Rails timezone names to PostgreSQL?

Question

I need to use the query similar to
SELECT * FROM items WHERE to_char(created_at AT TIME ZONE 'RAILS_GIVEN_ZONE', 'DD/MM/YYYY') ILIKE '%5/02%'

where the RAILS_GIVEN_ZONE value should always use the time zone from the Rails 4 app (which could be changed by the user), not the PG's timezone option.

But the problem is that the timezones from Rails and PG do not correspond 1-to-1 exactly.

Using the offset (as in +10:00 from Time.zone.now.formatted_offset) isn't good enough since in this case PG will not be able to deal with the daylight saving time correctly.

So the question is what is the best way to automatically map Rails current time zone (Time.zone) to the PostgreSQL's named time zone?

NOTE: the create_at column is timestamptz (stored as UTC, displayed in whatever zone is necessary)

Answer 1

There seems to be a MAPPING constant defined in ActiveSupport::TimeZone, which contains values that, unless I am mistaking, should all be supported by Postgres:

http://api.rubyonrails.org/classes/ActiveSupport/TimeZone.html

Per the docs:

Keys are Rails TimeZone names, values are TZInfo identifiers.

If you don't want to use the MAPPING constant directly, there's a find_tzinfo method in there, which seems to return aTZInfo::TimezoneProxy:

http://rubydoc.info/gems/tzinfo/TZInfo/TimezoneProxy

The latter sports an identifier method that should contain the needed string.