如何将 Rails 时区名称映射到 PostgreSQL? [英] How to map Rails timezone names to PostgreSQL?

问题描述

我需要使用类似于
的查询SELECT * FROM items WHERE to_char(created_at AT TIME ZONE 'RAILS_GIVEN_ZONE', 'DD/MM/YYYY') ILIKE '%5/02%'

RAILS_GIVEN_ZONE 值应始终使用 Rails 4 应用程序中的时区(可由用户更改)，而不是 PG 的 timezone 选项.>

但问题是 Rails 和 PG 的时区不是一一对应的.

使用偏移量(如 Time.zone.now.formatted_offset 中的 +10:00)不够好，因为在这种情况下 PG 将无法正确处理夏令时.

那么问题是自动将 Rails 当前时区 (Time.zone) 映射到 PostgreSQL 的 named 时区的最佳方法是什么?

注意:create_at 列是 timestamptz(存储为 UTC，显示在任何需要的区域)

解决方案

似乎在 ActiveSupport::TimeZone 中定义了一个 MAPPING 常量，其中包含的值是，除非我搞错了，都应该得到 Postgres 的支持:

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

根据文档:

<块引用>

键是 Rails 时区名称，值是 TZInfo 标识符.

如果你不想直接使用MAPPING常量，那里有一个find_tzinfo方法，它似乎返回一个TZInfo::TimezoneProxy:

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

后者运行一个 identifier 方法，该方法应该包含所需的字符串.

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)

解决方案

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.

这篇关于如何将 Rails 时区名称映射到 PostgreSQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！