当系统处于 TimeZone C 时，SQL 将 TimeStamp 列的 TimeZone 从 A 转换为 B

Question

BirthDatabase Oracle 11g 中的表有以下五列:

ID - 非空 - 号码

Name - Varchar2(32)

DOB - 日期

Place - Varchar2(32)

BirthTime - 时间戳(6)

BirthTime列不知道时区，但具有日期时间数据，位于 UTC .

但是，(最重要的)数据库的系统时间戳在 Europe\London .

这意味着 BirthTime数据不是 timezone意识到并且与系统 TZ 相比处于不同的 TZ 中。

任务:编写一个 SQL 查询来获取 BirthTime在伦敦本地时间(即，当 DST 关闭时为 UTC，而当 DST 为 ON 时为 UTC+1)

我的做法:

我试过玩 cast SQL 中的函数，但没有完全达到所需的输出

select
    ID, Name, DOB, Place,
    BirthTime as orig_BT,
    cast(BirthTime as timestamp with time zone) as BT_withTz, 
    cast(cast(BirthTime as timestamp with time zone) at time zone 'Europe/London' as timestamp) BT_BST,
    cast(cast(BirthTime as timestamp with time zone) at time zone 'UTC' as timestamp) BT_UTC
from
    BirthDatabase

这将返回以下输出:

ID  NAME    DOB         PLACE   ORIG_BT                         BT_WITHTZ                                   BT_BST                          BT_UTC
________________________________________________________________________________________________________________________________________________________________________
1   John    28-OCT-16   Bristol 28-OCT-16 10.48.12.000000000    28-OCT-16 10.48.12.000000000 EUROPE/LONDON  28-OCT-16 10.48.12.000000000    28-OCT-16 09.48.12.000000000
2   Jane    01-NOV-16   London  01-NOV-16 11.48.29.000000000    01-NOV-16 11.48.29.000000000 EUROPE/LONDON  01-NOV-16 11.48.29.000000000    01-NOV-16 11.48.29.000000000

我想要的输出如下:

ID  NAME    DOB         PLACE   ORIG_BT                         BT_DESIRED
____________________________________________________________________________________________
1   John    28-OCT-16   Bristol 28-OCT-16 10.48.12.000000000    28-OCT-16 11.48.12.000000000
2   Jane    01-NOV-16   London  01-NOV-16 11.48.29.000000000    01-NOV-16 11.48.29.000000000

我认为使用上述查询的输出获得 Desired 输出的一种快速而肮脏的方法是修改 SQL 以计算以下内容:

BT_DESRIED = ORIG_BT + (BT_BST - BT_UTC)

我的问题如下:

计算上述 BT_DESRIED = ORIG_BT + (BT_BST - BT_UTC) 表达式的语法是什么？

有没有更优雅的方法来实现这一目标？

Answer 1

您可以使用以下查询:

select
   ID, Name, DOB, Place, BirthTime as orig_BT,
   FROM_TZ(BirthTime, 'UTC') AT TIME ZONE 'Europe/London' AS BT_BST
FROM BirthDatabase;

当您制作 cast(BirthTime as timestamp with time zone) Oracle 需要您的 SESSIONTIMEZONE 用于转换 - 不是数据库系统时区。
cast(BirthTime as timestamp with time zone) at time zone 'Europe/London'执行 alter session set time_zone = 'UTC'; 时会给出正确的结果预先。