postgresql Rails：如何在 postgres 上创建带有时区的时间列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19207856/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails:How to create a time column with timezone on postgres
提问by G Sree Teja Simha
I have a railsapplication which works on postgresdb. I have a model called UserTimings where there are two fields from_time and to_time.
我有一个适用于postgresdb的rails应用程序。我有一个名为 UserTimings 的模型，其中有两个字段 from_time 和 to_time。
t.time "from_time"
t.time "to_time"
I expected that the time will be stored in complete UTC format with timezone information. After a little while, I realized that the database has this kind SQL query to create the table.
我预计时间将以带有时区信息的完整 UTC 格式存储。过了一会儿，我意识到数据库有这种 SQL 查询来创建表。
from_time time without time zone,
to_time time without time zone,
I do not want this. I want to store with time zone. I want the +0530 thingy in the UTC time which I'm not getting althought the rails application has been configured to handle it. Please tell me how to write a migration to force the db to use the time zone.
我不想要这个。我想用time zone存储。我想要 UTC 时间的 +0530 东西，虽然 Rails 应用程序已配置为处理它，但我没有得到它。请告诉我如何编写迁移以强制数据库使用时区。
Thank you.
谢谢你。
回答by Dimitri
That migration do exactly what you want
该迁移完全符合您的要求
class CreatePeppers < ActiveRecord::Migration
def change
create_table :peppers do |t|
t.column :runs, 'timestamp with time zone'
t.column :stops, 'time with time zone'
end
end
end
Types are for your choice. You can write here any type, that postgresql supports. But there may be problems with conversion to types, that rails can understand.
类型供您选择。您可以在此处编写 postgresql 支持的任何类型。但是转换为类型可能会出现问题，rails 可以理解。
回答by Richard Huxton
You seem to be confused about two separate things here - both common mistakes, almost everyone makes at least one of them.
你似乎对这里的两个不同的事情感到困惑——都是常见的错误，几乎每个人都至少犯过一个。
Firstly "UTC format with timezone information" isn't specifying a time. It's specifying time AND place.
首先，“带有时区信息的 UTC 格式”没有指定时间。它指定了时间和地点。
Secondly PostgreSQL's "timestamp with time zone" doesn't in fact store a time zone. It stores a UTC timestamp but it acceptsa time zone. A better choice of name would be something like "absolute time". You can compare two of these values directly.
其次，PostgreSQL 的“带时区的时间戳”实际上并不存储时区。它存储一个 UTC 时间戳，但它接受一个时区。更好的名称选择类似于“绝对时间”。您可以直接比较其中的两个值。
A timestamp without time zone doesn't actually give you a time unless you also have a place. You can't compare two of these unless you know which timezone each is in.
没有时区的时间戳实际上不会给你时间，除非你也有一个地方。除非您知道每个所在的时区，否则您无法比较其中的两个。
It sounds like what you want isa timestamp without time zone and a separate timezone. That way you can say "2pm on Tuesday, London Time".
听起来您想要的是一个没有时区的时间戳和一个单独的时区。这样你就可以说“伦敦时间星期二下午 2 点”。
回答by HFX
Not sure this is what your want, but this works for me:
不确定这是你想要的，但这对我有用：
add_column :table, :from_time, :timetz
add_column :table, :to_time, :timetz
But rails seems cannot to recognize timetz type, it will be treated as String. To work around this, i register timetz as time.
但 rails 似乎无法识别 timetz 类型，它将被视为字符串。为了解决这个问题，我将 timetz 注册为时间。
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.class_eval do
def initialize_type_map_with_postgres_oids mapping
initialize_type_map_without_postgres_oids mapping
register_class_with_limit mapping, 'timetz',
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::OID::Time
end
alias_method_chain :initialize_type_map, :postgres_oids
end
BTW, my environment is rails-4.2
顺便说一句，我的环境是 rails-4.2
