Oracle SQL：将时间戳转换为 UTC

Oracle SQL: converting timestamp to UTC

提问by Fearghal

I have a simple select query such as below but I noticed I am getting back the regional times. How can I convert to UTC in my select statment?

我有一个简单的选择查询，如下所示，但我注意到我回到了区域时间。如何在我的选择语句中转换为 UTC？

select myTimeStamp, MyName, MyBranch from tableA

Result: '27/03/2014 15:15:26' 'john', 'london'

结果：“27/03/2014 15:15:26”“约翰”、“伦敦”

I have tried using sys_extract_utc (myTimeStamp) but I have the error

我曾尝试使用 sys_extract_utc (myTimeStamp) 但出现错误

sql command not properly ended

sql命令没有正确结束

The column myTimestampis of type 'date'.

该列myTimestamp的类型为“日期”。

回答by a_horse_with_no_name

select cast(mytimestamp as timestamp) at time zone 'UTC', 
       MyName, 
       MyBranch 
from tableA

Because mytimestampis in fact a datenot a timestamp you need to cast it. Doing that makes Oracle assume that the information stored in mytimestampis in the time zone of the server -if that isn't the case you need to use Madhawas' solution.

因为mytimestamp实际上date不是您需要投射的时间戳。这样做会使 Oracle 假定存储在mytimestamp服务器的时区中的信息 - 如果不是这种情况，您需要使用 Madhawas 的解决方案。

回答by Chris R. Donnelly

Depending on the type, there are a couple of gotchas with regard to what time zone Oracle is converting from depending on what the data type of myTimestampis.

根据类型的不同，根据数据类型的不同，关于 Oracle 正在转换的时区有几个问题myTimestamp。

timestamp with time zone

带时区的时间戳

It Just Works?. a_horse_with_no_namehas the right answer here.

它只是有效吗？。 a_horse_with_no_name在这里有正确的答案。

timestamp with local time zone

带有本地时区的时间戳

it is implicitly cast to timestamp with time zone, then It Just Works?. Again, a_horse_with_no_nameis right here.

它被隐式转换为带 time zone 的时间戳，然后 It Just Works?。同样，a_horse_with_no_name就在这里。

timestamp

时间戳

While it too is implicitly cast to timestamp with time zone, the time zone that gets assigned by default is the sessiontime zone (as opposed to the databasetime zone).

虽然它也被隐式转换为带时区的时间戳，但默认分配的时区是会话时区（与数据库时区相反）。

• The explicit invocation of this is myTimestamp at local.
• Alternatively (and most likely better), you can do as Madhawassays and use the from_tzfunction to explicitly build a value with an explicit time zone other than that of your session.

• 显式调用 this 是myTimestamp at local.
• 或者（并且很可能更好），您可以按照Madhawas所说的去做，并使用该from_tz函数显式构建一个具有显式时区而不是会话时区的值。

date

日期

Trying to do any of the above to datewill fail as you described:

迄今为止，尝试执行上述任何操作都将失败，如您所述：

• myTimestamp at time zone 'UTC'
  ORA-30084: invalid data type for datetime primary with time zone modifier
  
  
• from_tz(myTimestamp, 'America/New_York')
  ORA-00932: inconsistent datatypes: expected TIMESTAMP got DATE
  
  

• myTimestamp at time zone 'UTC'
  ORA-30084: 带有时区修饰符的 datetime 主数据类型无效
  
  
• from_tz(myTimestamp, 'America/New_York')
  ORA-00932: 不一致的数据类型：预期的 TIMESTAMP 得到了 DATE
  
  

The solution here is to cast the date to a timestampfirst:

这里的解决方案是先将日期转换为时间戳：

select from_tz(cast(myTimestamp as timestamp), 'America/New_York') from tableA

Sample Script

示例脚本

The following script illustrates the behavior. Note that on my system, dbtimezoneis US/Central, and sessiontimezoneis GMT-05:00.

以下脚本说明了该行为。请注意，在我的系统上，dbtimezone是美国/中部，并且sessiontimezone是 GMT-05:00。

I also use to_charto convert the output as I have found some tools will alter the result timestamp in subtle ways, particularly if they don't have good timestamp support (this is rare nowadays, but still potentially a problem).

我还使用to_char转换输出，因为我发现一些工具会以微妙的方式改变结果时间戳，特别是如果它们没有良好的时间戳支持（这在现在很少见，但仍然可能是一个问题）。

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'
/
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS'
/
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS TZR'
/

select dbtimezone
      ,sessiontimezone
      ,to_char(timestamp '2017-01-01 06:00:00') as ts
      ,to_char(timestamp '2017-01-01 06:00:00' at local) as ts_at_local
      ,to_char(timestamp '2017-01-01 06:00:00' at time zone dbtimezone) as ts_at_db
      ,to_char(timestamp '2017-01-01 06:00:00' at time zone sessiontimezone) as ts_at_session
 from dual
/

The output on my system is as follows (reformatted as columnar for readability):

我的系统上的输出如下（重新格式化为柱状以提高可读性）：

DBTIMEZONE          US/Central
SESSIONTIMEZONE     -05:00
TS                  2017-01-01 06:00:00
TS_AT_LOCAL         2017-01-01 06:00:00 -05:00
TS_AT_DB            2017-01-01 05:00:00 US/CENTRAL
TS_AT_SESSION       2017-01-01 06:00:00 -05:00

回答by Madhawas

You need to know your time zone for this;

为此，您需要知道您的时区；

SELECT myTimeStamp, from_tz(myTimeStamp, 'America/New_York') AT TIME ZONE 'UTC' utc FROM dual;

回答by Kaushik Nayak

Starting from Oracle 19c, a new function is introduced which is TO_UTC_TIMESTAMP_TZ

从 Oracle 19c 开始，引入了一个新功能，即 TO_UTC_TIMESTAMP_TZ

The SQL function TO_UTC_TIMESTAMP_TZ takes an ISO 8601 date format string as the varchar input and returns an instance of SQL data type TIMESTAMP WITH TIMEZONE. It normalizes the input to UTC time (Coordinated Universal Time, formerly Greenwich Mean Time). Unlike SQL function TO_TIMESTAMP_TZ , the new function assumes that the input string uses the ISO 8601 date format, defaulting the time zone to UTC 0.

SQL 函数 TO_UTC_TIMESTAMP_TZ 将 ISO 8601 日期格式字符串作为 varchar 输入并返回 SQL 数据类型 TIMESTAMP WITH TIMEZONE 的实例。它将输入标准化为 UTC 时间（协调世界时，以前称为格林威治标准时间）。与 SQL 函数 TO_TIMESTAMP_TZ 不同，新函数假定输入字符串使用 ISO 8601 日期格式，时区默认为 UTC 0。

select TO_UTC_TIMESTAMP_TZ ( to_char(sysdate,'yyyy-mm-dd"T"HH:MI:SS') )  as utc 
   from dual;

 UTC
31-MAR-19 05.45.36.000000 AM +00:00