如何将当地时间转换为UTC?

本文介绍了如何将当地时间转换为UTC?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要使用函数将本地时间转换为 UTC.我的输入是当地时间，当地时间的时区(例如太平洋/奥克兰").我需要从该过程中获得的是基于给定时区的本地时间的 UTC 时间.

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.

有人可以帮忙吗?

我使用的是 8.3 版

I am using version 8.3

推荐答案

这在 手册，但如何实际处理日期/时间并不总是显而易见的.SQL 规范有点奇怪.

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.

就您的问题而言，不清楚您是要以 UTC 存储时间但以服务器的本地时间 (TimeZone) 显示，还是要忽略 TimeZone 并始终将其显示为 UTC.我会假设后者.

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 TimeZone and always display it as UTC. I'll assume the latter.

对于时间戳，您只需使用 AT TIME ZONE 两次，例如:

For timestamps you'd just use AT TIME ZONE twice, like:

SELECT TIMESTAMP '2013-08-13 00:00:00' AT TIME ZONE 'Australia/Sydney' AT TIME ZONE 'UTC';

您需要使用 AT TIME ZONE 两次.一次根据参数时区将输入 timestamp 转换为 timestamptz，然后将其转换为 UTC 的 timestamp.

You need to use AT TIME ZONE twice. Once to convert the input timestamp to timestamptz according to the argument timezone, then another to convert that to a timestamp at UTC.

不幸的是，由于 SQL 规范为 TIME 定义 AT TIME ZONE 的(IMO 疯狂)方式，您不能为 TIME.您必须改为操作 TimeZone 变量:

Unfortunately because of the (IMO insane) way the SQL spec defines AT TIME ZONE for TIME, you can't do the same thing for TIME. You'll have to manipulate the TimeZone variable instead:

SET TimeZone = 'UTC'; SELECT TIME '07:00' AT TIME ZONE 'Australia/Sydney';

这仍然给你一个 timetz 而不是 time.所以它的显示值会随着 timezone 设置而变化.

This still leaves you with a timetz not a time. So its display value changes with the timezone setting.