postgresql 在 SQL 中从 POSTGRE/SQL 数据库转换 UTC 时间字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7065946/
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
Converting UTC time field from a POSTGRE/SQL database in SQL
提问by Gwenael
I am having a problem from my database. I never been used to work in PostgreSQL, and i would like to make a select from a UTC datetime field and getting a GMT Datetime as result.
我的数据库有问题。我从未习惯于在 PostgreSQL 中工作，我想从 UTC 日期时间字段中进行选择并获得 GMT 日期时间作为结果。
Actually my request is : select dateheure from position What should be the Postgresql request to do what i want to do ???
实际上我的请求是：从位置选择 dateheure 什么应该是 Postgresql 请求来做我想做的事情？？？
Thanks a lot Gwenael
非常感谢格温尼尔
回答by Grzegorz Szpetkowski
PostgreSQL does have two datetime types:
PostgreSQL 确实有两种日期时间类型：
timestamp without time zone(default implicittimestamp)timestamp with time zone
timestamp without time zone（默认隐式timestamp）timestamp with time zone
I guess that you have table with UTC datetime (without time zone type):
我猜您有带有 UTC 日期时间的表（没有时区类型）：
CREATE TEMP TABLE datetimetest
(
datetime timestamp
);
\d datetimetest
Table "pg_temp_1.datetimetest"
Column | Type | Modifiers
----------+-----------------------------+-----------
datetime | timestamp without time zone |
INSERT INTO datetimetest SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
SELECT datetime FROM datetimetest;
datetime
----------------------------
2011-08-15 15:04:06.507166
(1 row)
To get datetime in some timezone you could use AT TIME ZONEconstruct:
要在某个时区获取日期时间，您可以使用AT TIME ZONE构造：
SET TIME ZONE 'UTC';
SELECT datetime AT TIME ZONE 'GMT-5' FROM datetimetest;
timezone
-------------------------------
2011-08-15 10:04:06.507166+00
(1 row)
回答by Sean
In a different postI use a CHECK()constraint to make sure that you only store and receive UTCout of the database. Hopefully it's helpful.
在另一篇文章中，我使用CHECK()约束来确保您只UTC从数据库中存储和接收数据。希望它有帮助。
