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)

"isn't good enough since in this case PG will not be able to deal with the daylight saving time correctly." - don't you mean: because Rails isn't dealing with DST correctly? (PG deals with it just fine, with the timestamptz type.) — Denis de Bernardy
– Denis de Bernardy, Commented May 2, 2014 at 5:32
@Denis no PG physically can't understand daylight savings just from the offset since there are often multiple zones for that offset. Read postgresql.org/docs/9.1/static/… — Dmytrii Nagirniak
– Dmytrii Nagirniak, Commented May 2, 2014 at 5:36
Doesn't answer the question, but potentially interesting reading on the topic: stackoverflow.com/questions/9571392/… — Denis de Bernardy
– Denis de Bernardy, Commented May 2, 2014 at 5:36
@Denis "A time zone abbreviation, for example PST. Such a specification merely defines a particular offset from UTC, in contrast to full time zone names which can imply a set of daylight savings transition-date rules as well. The recognized abbreviations are listed in the pg_timezone_abbrevs view (see Section 45.66). You cannot set the configuration parameters timezone or log_timezone to a time zone abbreviation, but you can use abbreviations in date/time input values and with the AT TIME ZONE operator." — Dmytrii Nagirniak
– Dmytrii Nagirniak, Commented May 2, 2014 at 5:37
@Denis thanks for the link to the other SO answer. But I'm well aware of that one and it isn't related to my question here at all. — Dmytrii Nagirniak
– Dmytrii Nagirniak, Commented May 2, 2014 at 5:38

Denis de Bernardy · Accepted Answer · 2014-05-02 05:57:57Z

2

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.

edited May 2, 2014 at 5:57

answered May 2, 2014 at 5:49

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

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

1 Comment

lucas Over a year ago

Thanks Denis de Bernardy! So you are saying one could write ActiveSupport::TimeZone.find_tzinfo(Time.zone.name).identifier to get the PostgreSQL identifier for the app's default timezone. Oof, anyone know of a more concise way to do that?

Collectives™ on Stack Overflow

How to map Rails timezone names to PostgreSQL?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related