5

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)

Dmytrii Nagirniak
  • 23,696
  • 13
  • 75
  • 130
  • "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 May 02 '14 at 05:32
  • @Denis no PG physically can't understand daylight savings just from the offset since there are often multiple zones for that offset. Read http://www.postgresql.org/docs/9.1/static/datatype-datetime.html#DATATYPE-TIMEZONES – Dmytrii Nagirniak May 02 '14 at 05:36
  • Doesn't answer the question, but potentially interesting reading on the topic: http://stackoverflow.com/questions/9571392/ignoring-timezones-altogether-in-rails-and-postgresql – Denis de Bernardy May 02 '14 at 05: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 May 02 '14 at 05: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 May 02 '14 at 05:38
  • Yayaya, and yes, we're both clear on the topic that the offset doesn't include dst, but PG can (indeed will) compute the correct time with dst when asked -- and therefor your question. +1 good question, btw. :-) – Denis de Bernardy May 02 '14 at 05:39

1 Answers1

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.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • 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? – lucas Nov 20 '17 at 00:03