使用TIMEZONE查询Oracle TIMESTAMP [英] Querying Oracle TIMESTAMP WITH TIMEZONE

问题描述

我在Oracle DB表中有一个列，列的类型为TIMESTAMP(6) WITH TIME ZONE.数据行中包含来自不同时区的数据，某些UTC，某些时区的偏移量.

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表，使结果始终以UTC的形式返回，并进行了适当的时移?是否可以对查询本身执行某些操作，或者以某种方式更改会话?我尝试将会话时区更改为Utc，但这似乎只会影响CURRENT_TIMESTAMP值.

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.

ALTER SESSION SET TIME_ZONE = 'Utc'

例如，如果值存储为:

21-JAN-10 03.28.38.635000000 PM -05:00

查询将返回为

21-JAN-10 08.28.38.635000000 PM Utc

表格定义示例

CREATE TABLE "MyDb"."Books"
  (
    "GUID" RAW(32) DEFAULT SYS_GUID(),
     "DATE_CREATED" TIMESTAMP (6) WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
);

推荐答案

您应该能够使用AT TIME ZONE语法

SELECT column_name at time zone 'UTC'
  FROM your_table

即

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

这篇关于使用TIMEZONE查询Oracle TIMESTAMP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！