postgresql 如何将当地时间转换为UTC？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18188151/
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
How to convert local time to UTC?
提问by Arun
I need to convert local time to UTC using a function. The inputs I have is the local time, timezone of the local time (e.g. 'Pacific/Auckland'). What I need to get from the procedure is the UTC time for the local time based on given timezone.
我需要使用函数将本地时间转换为 UTC。我的输入是当地时间，当地时间的时区（例如“太平洋/奥克兰”）。我需要从该过程中获得的是基于给定时区的本地时间的 UTC 时间。
Can someone please help?
有人可以帮忙吗？
I am using version 8.3
我使用的是 8.3 版
回答by Craig Ringer
This is covered in the manual, but it's not always obvious how to actually work with dates/times. The SQL spec is a bit bizarre.
这在手册中有介绍，但如何实际处理日期/时间并不总是很明显。SQL 规范有点奇怪。
In the case of your question it isn't clear whether you want to store the time in UTC but display it in the server's local time (TimeZone), or whether you wan to ignore TimeZoneand always display it as UTC. I'll assume the latter.
在您的问题的情况下，不清楚您是要以 UTC 存储时间但以服务器的本地时间 ( TimeZone)显示它，还是要忽略TimeZone并始终将其显示为 UTC。我会假设后者。
For timestamps you'd just use AT TIME ZONEtwice, like:
对于您只需使用AT TIME ZONE两次的时间戳，例如：
SELECT TIMESTAMP '2013-08-13 00:00:00' AT TIME ZONE 'Australia/Sydney' AT TIME ZONE 'UTC';
You need to use AT TIME ZONEtwice. Once to convert the input timestampto timestamptzaccording to the argument timezone, then another to convert that to a timestampat UTC.
您需要使用AT TIME ZONE两次。一旦将输入转换timestamp到timestamptz根据的说法时区，然后另一个将其转换成一个timestamp在UTC。
Unfortunately because of the (IMO insane) way the SQL spec defines AT TIME ZONEfor TIME, you can't do the same thing for TIME. You'll have to manipulate the TimeZonevariable instead:
不幸的是因为（IMO疯狂）的方式SQL规范定义AT TIME ZONE的TIME，你不能做同样的事情TIME。您必须改为操作TimeZone变量：
SET TimeZone = 'UTC';
SELECT TIME '07:00' AT TIME ZONE 'Australia/Sydney';
This still leaves you with a timetznot a time. So its display value changes with the timezonesetting.
这仍然给你一个timetznot a time。所以它的显示值随着timezone设置的变化而变化。
