SQL 使用 TIMEZONE 查询 Oracle TIMESTAMP
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9216832/
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
Querying Oracle TIMESTAMP WITH TIMEZONE
提问by Stealth Rabbi
I have a column in an Oracle DB table that is of type TIMESTAMP(6) WITH TIME ZONE. There are data rows with data from different timezones, some UTC, some in other timezone offsets.
我在 Oracle DB 表中有一个类型为 的列TIMESTAMP(6) WITH TIME ZONE。有些数据行包含来自不同时区的数据，有些是 UTC，有些是其他时区偏移量。
Is there a way I can query the Oracle table so that the results always come back as UTC, with the appropriate time shifting being done? Is there something that can be done on the query itself, or perhaps altering the session somehow? I've tried altering the session timezone to Utc, but this seems to only impact the CURRENT_TIMESTAMP value.
有没有办法可以查询 Oracle 表，以便结果始终以 UTC 形式返回，并进行适当的时移？是否可以对查询本身进行某些操作，或者以某种方式更改会话？我尝试将会话时区更改为 Utc，但这似乎只影响 CURRENT_TIMESTAMP 值。
ALTER SESSION SET TIME_ZONE = 'Utc'
For example, if a value was stored as:
例如，如果一个值存储为：
21-JAN-10 03.28.38.635000000 PM -05:00
the query would come back as
查询将返回为
21-JAN-10 08.28.38.635000000 PM Utc
Example table definition
示例表定义
CREATE TABLE "MyDb"."Books"
(
"GUID" RAW(32) DEFAULT SYS_GUID(),
"DATE_CREATED" TIMESTAMP (6) WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
);
回答by Justin Cave
You should be able to use the AT TIME ZONEsyntax
您应该能够使用AT TIME ZONE语法
SELECT column_name at time zone 'UTC'
FROM your_table
i.e.
IE
SQL> select * from foo;
COL1
---------------------------------------------------------------------------
09-FEB-12 01.48.40.072000 PM -05:00
09-FEB-12 10.49.26.613000 AM US/PACIFIC
SQL> select col1 at time zone 'UTC'
2 from foo;
COL1ATTIMEZONE'UTC'
---------------------------------------------------------------------------
09-FEB-12 06.48.40.072000 PM UTC
09-FEB-12 06.49.26.613000 PM UTC
