postgresql 将没有时区的时间戳转换为整数 Postgres
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28155683/
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
Convert Timestamp without timezone to integer Postgres
提问by CogitoErgoSum
I've inherited a database with a quirky schema. Without boring of the details I have an odd use case.
我继承了一个具有古怪架构的数据库。没有无聊的细节，我有一个奇怪的用例。
The table contains a filed completed_at that is a timestamp without timezone. What I'm trying to do is order the results by the timestamp where the timestamp is > X
该表包含一个已归档的 Completed_at，它是一个没有时区的时间戳。我想要做的是按时间戳排序结果，其中时间戳 > X
The kicker is X is a numeric (or decimal) value. Trying to convert the timestamp to int is failing or even to string.
踢球者是 X 是一个数字（或十进制）值。尝试将时间戳转换为 int 失败甚至转换为字符串。
If you simply try completed_at > 0 you get
如果你简单地尝试 completed_at > 0 你会得到
回答by Stas Shafeev
The only answer I found was in this message: https://www.postgresql.org/message-id/3EE8B4ED.1060200%40pgsqldb.com
我找到的唯一答案是在这条消息中：https: //www.postgresql.org/message-id/3EE8B4ED.1060200%40pgsqldb.com
Basically, I guess, the answer is:
select cast(extract(epoch from current_timestamp) as integer);
基本上，我想，答案是：
select cast(extract(epoch from current_timestamp) as integer);
回答by Michael Spagon
I had a similar problem: I wanted to extract the year as an Integer, so using the above I wrote CAST(EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS INTEGER)into my PostgreSQL database and then examined the constraint in PGADMIN 4 and it came up with the following alternative, which I found interesting:
我有一个类似的问题：我想将年份提取为整数，所以使用上面的内容我写入CAST(EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS INTEGER)了我的 PostgreSQL 数据库，然后检查了 PGADMIN 4 中的约束，它提出了以下替代方案，我觉得这很有趣：
date_part('year'::text, CURRENT_TIMESTAMP)::integer
date_part('year'::text, CURRENT_TIMESTAMP)::integer
回答by Raunak Chowdhury
I am using the below code to convert the current time to Bigint.
我正在使用以下代码将当前时间转换为 Bigint。
SELECT cast(to_char((current_timestamp)::TIMESTAMP,'yyyymmddhhmiss') as BigInt)
回答by kayleighsdaddy
I would use the following code if it is SQL. It works for me. Change the ASC to DESC if it is the wrong way. You should not need to convert anything.
如果是 SQL，我会使用以下代码。这个对我有用。如果方法错误，请将 ASC 更改为 DESC。您不需要转换任何内容。
ORDER BY `timestamp_column` ASC
