将 postgresql 中的 UTC 时区转换为 EST（本地时间）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36341060/
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 a UTC timezone in postgresql to EST (local time)
提问by Subu Ganesh
I am new to PostgreSQL and I was wondering if there is a direct way to just convert the timestamp values in a table to a different timezone using a function. In my case it is UTC to EST.
我是 PostgreSQL 的新手，我想知道是否有一种直接的方法可以使用函数将表中的时间戳值转换为不同的时区。在我的情况下，它是 UTC 到 EST。
These are the values for example that I need to convert to EST (not just one value but all the values in the table)
例如，这些是我需要转换为 EST 的值（不仅仅是一个值，而是表中的所有值）
date
-------------------
2015-10-24 16:38:46
2016-01-19 18:27:00
2016-01-24 16:14:34
2016-02-09 23:05:49
2016-02-11 20:46:26
回答by Richard Huxton
Here in London, we are currently 1 hour ahead of UTC. So - if I take your timezone without timestamp and say it is in UTC I will get it printed for my local timezone.
在伦敦，我们目前比 UTC 早 1 小时。所以 - 如果我使用没有时间戳的时区并说它是 UTC，我会为我的本地时区打印它。
richardh=> SELECT ((timestamp '2015-10-24 16:38:46') AT TIME ZONE 'UTC');
timezone
------------------------
2015-10-24 17:38:46+01
(1 row)
But you want "EST" which seems to be somewhere in the Americas, judging by the value returned. You can wrap the expression in a little SQL function if you wanted to.
但是你想要“EST”，它似乎在美洲的某个地方，从返回的值来看。如果需要，您可以将表达式包装在一个小的 SQL 函数中。
richardh=> SELECT ((timestamp '2015-10-24 16:38:46') AT TIME ZONE 'UTC') AT TIME ZONE 'EST';
timezone
---------------------
2015-10-24 11:38:46
(1 row)
Edit: how to do it in a query
编辑：如何在查询中进行
SELECT ((stored_timestamp AT TIME ZONE 'UTC') AT TIME ZONE 'EST') AS local_timestamp
FROM my_table;
You will probably want to get an introductory book on SQL if this sort of thing is causing you problems.
如果这类事情给您带来了问题，您可能希望获得一本关于 SQL 的介绍性书籍。
回答by Kevin Li
Similarly execute
同样执行
SELECT '2015-10-24 16:38:46'::timestamp AT time zone 'EST';
timezone
------------------------
2015-10-24 21:38:46+00
(1 row)
回答by user33072
You should always store the main reference of a date in UTC and either convert it to a time zone in your queries or store the specific timezone version of the data in another column. The reason for this is that it is quick and easy to convert a date from UTC to another time zone as long as you know that the timezone that it is stored as is UTC. It takes the guess work out of it. Alternatively, you can store the date WITH the timezone.
您应该始终以 UTC 格式存储日期的主要引用，并将其转换为查询中的时区或将数据的特定时区版本存储在另一列中。这样做的原因是，只要您知道它存储的时区是 UTC，就可以快速轻松地将日期从 UTC 转换为另一个时区。它消除了猜测工作。或者，您可以存储带有时区的日期。
If you have an operation that automatically populates the date with the system clock of your server, then you can either A: Change the operation to use UTC time B: Change the system clock on the server to UTC
如果您的操作自动使用服务器的系统时钟填充日期，那么您可以 A：将操作更改为使用 UTC 时间 B：将服务器上的系统时钟更改为 UTC
回答by Elmira Behzad
I had the same problem, I am working with different regions and timezones, I need to just fix the timezone in the query the way it doesn't effect other customers around the regions and I havent changed the table structure or any thing(Open–closed principle) . What I did In my query: SELECT TO_CHAR(current_timestamp at time zone 'Australia/Melbourne', 'DD/MM/YYYY hh24:mi AM') as date_of_extract This worked for me and I could change the 'UTC' defult timezone for my postgressql to the 'Australia/Melbourne'(any time zone you are looking into). hope this is helpful.
我遇到了同样的问题，我正在使用不同的地区和时区，我只需要在查询中修复时区，因为它不会影响该地区的其他客户，而且我没有更改表结构或任何事情（打开 -封闭原则）。我在我的查询中做了什么： SELECT TO_CHAR(current_timestamp at time zone 'Australia/Melbourne', 'DD/MM/YYYY hh24:mi AM') as date_of_extract 这对我有用，我可以更改我的“UTC”默认时区postgressql 到“澳大利亚/墨尔本”（您正在查看的任何时区）。希望这是有帮助的。
回答by Leandro Castro
I usually leave everything in UTC and convert when it is time to show. I use something like:
我通常将所有内容保留在 UTC 中，并在需要显示时进行转换。我使用类似的东西：
SELECT my_date_utc AT time zone 'utc' at time zone 'est' From ....
