postgresql Unix时间戳的PostgreSQL字段类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11799160/
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
PostgreSQL field type for unix timestamp?
提问by ThinkingMonkey
PostgreSQL field type for unix timestamp :
Unix 时间戳的 PostgreSQL 字段类型：
- to store it as unix time stamp
- to retrieve it as a unix timestamp as well.
- 将其存储为 Unix 时间戳
- 也可以将其作为 unix 时间戳进行检索。
Have been going through Date/Time Types postgreSQL V 9.1.
已经通过了PostgreSQL V 9.1 的日期/时间类型。
- Is integer the best way to go!? (this is what I had done when I was using MySQL. Had used
int(10))
- 整数是最好的方法吗！？（这是我在使用 MySQL 时所做的。曾经使用过
int(10)）
采纳答案by CyberDem0n
integer would be good, but not enough good, because postgresql doesn't support unsigned types
整数会很好，但还不够好，因为 postgresql 不支持无符号类型
回答by Richard
The unix epoch timestamp right now (2014-04-09) is 1397071518. So we need an data type capable of storing a number at least this large.
现在 (2014-04-09) 的 unix 纪元时间戳是 1397071518。所以我们需要一种能够存储至少这么大的数字的数据类型。
What data types are available?
有哪些数据类型可用？
If you refer to the PostgreSQL documentation on numeric typesyou'll find the following options:
如果您参考有关数字类型的 PostgreSQL文档，您会发现以下选项：
Name Size Minimum Maximum
smallint 2 bytes -32768 +32767
integer 4 bytes -2147483648 +2147483647
bigint 8 bytes -9223372036854775808 +9223372036854775807
What does that mean in terms of time representation?
这在时间表示方面意味着什么？
Now, we can take those numbers and convert them into dates using an epoch converter:
现在，我们可以获取这些数字并使用纪元转换器将它们转换为日期：
Name Size Minimum Date Maximum Date
smallint 2 bytes 1969-12-31 1970-01-01
integer 4 bytes 1901-12-13 2038-01-18
bigint 8 bytes -292275055-05-16 292278994-08-17
Note that in the last instance, using seconds puts you so far into the past and the future that it probably doesn't matter. The result I've given is for if you represent the unix epoch in milliseconds.
请注意，在最后一个实例中，使用秒会将您带入过去和未来，这可能无关紧要。我给出的结果是如果你以毫秒为单位表示 unix 纪元。
So, what have we learned?
所以我们学了什么？
smallintis clearly a bad choice.integeris a decent choice for the moment, but your software will blow up in the year 2038. The Y2K apocalypse has nothing on the Year 2038 Problem.- Using
bigintis the best choice.This is future-proofed against most conceivable human needs, though the Doctormay still criticiseit.
smallint显然是个糟糕的选择。integer目前是一个不错的选择，但是您的软件将在 2038 年爆炸。千年虫世界末日与2038 年问题无关。- 使用
bigint是最好的选择。这对于大多数可以想象的人类需求来说是面向未来的，尽管博士可能仍然会批评它。
You may or may not consider whether it might not be best to store your timestamp in another format such as the ISO 8601standard.
您可能会也可能不会考虑以其他格式（例如ISO 8601标准）存储时间戳是否最好。
回答by GordonM
I'd just go with using TIMESTAMP WITH(OUT) TIME ZONE and use EXTRACT to get a UNIX timestamp representation when you need one.
我只是使用 TIMESTAMP WITH(OUT) TIME ZONE 并在需要时使用 EXTRACT 来获取 UNIX 时间戳表示。
Compare
相比
SELECT NOW();
with
和
SELECT EXTRACT(EPOCH FROM NOW());
回答by jap1968
I do not understand why the question has some negative votes.
我不明白为什么这个问题有一些反对票。
Anyway, I have found a closely related questionin the Database Administratorssite (with many positive votes).
无论如何，我在数据库管理员站点中发现了一个密切相关的问题（有很多赞成票）。
This is just to suggest to have a look there, since there is a much more complete information about this non-trivial topic.
这只是建议去那里看看，因为有关于这个重要主题的更完整的信息。
