Oracle如何使用任何时区将时间戳转换为数据库服务器时区

Question

我有一个时间戳列，其中包含具有不同时区的数据。我需要选择属于数据库服务器时区给定" Day"的记录。

例如，如果MY_TIMESTAMP中的数据为19-MAR-19 00.37.56.030000000 EUROPE/PARIS。
以及在给定的日期19-MAR-19（也表示为2019078），其中数据库服务器在EUROPE/LONDON上。有什么方法，以便如果我的数据库服务器在EUROPE/LONDON时区中，则该记录将被忽略，但是如果它在EUROPE/PARIS中，则选择该记录。

。

请注意给定的时间戳是一天中的第一个小时，EUROPE/PARIS比EUROPE/LONDON领先一小时

我尝试的查询，不幸的是忽略了时间戳的时区。

select * from MY_TABLE where to_number(to_char(CAST(MY_TIMESTAMP AS DATE), 'RRRRDDD')) between 2019078 AND 2019079

以下提到的方法可以将时间戳从知识时区转换为所需时区中的日期，但是我不能使用此逻辑，因为源时区不知道。

CAST((FROM_TZ(CAST(MY_TIMESTAMP AS TIMESTAMP),'EUROPE/PARIS') AT TIME ZONE 'EUROPE/LONDON') AS DATE)'

Answer 1

您不需要转换表数据；除了做更多的工作之外，这样做还可以阻止使用该列的任何索引。

Oracle在比较值时会尊重时区，因此将原始表数据与特定日子进行比较 - 并将其转换为时间戳与时区：

select * 
from MY_TABLE 
where MY_TIMESTAMP >= timestamp '2019-03-19 00:00:00 Europe/London'
and   MY_TIMESTAMP <  timestamp '2019-03-20 00:00:00 Europe/London'

，或者如果您想将其基于今天而不是固定日期：

where MY_TIMESTAMP >= from_tz(cast(trunc(sysdate) as timestamp), 'Europe/London')
and   MY_TIMESTAMP <  from_tz(cast(trunc(sysdate) + 1 as timestamp), 'Europe/London')

或如果您通过日期为yyyyddd值（用数字参数名称替换固定值）：

where MY_TIMESTAMP >= from_tz(to_timestamp(to_char(2019078), 'RRRRDDD'), 'Europe/London')
and   MY_TIMESTAMP <  from_tz(to_timestamp(to_char(2019079), 'RRRRDDD'), 'Europe/London')

快速演示在CTE中使用一些示例数据，在两个区域中为简单起见：

with my_table (id, my_timestamp) as (
  select 1, timestamp '2019-03-19 00:37:56.030000000 Europe/Paris' from dual
  union all
  select 2, timestamp '2019-03-19 00:37:56.030000000 Europe/London' from dual
  union all
  select 3, timestamp '2019-03-19 01:00:00.000000000 Europe/Paris' from dual
  union all
  select 4, timestamp '2019-03-20 00:37:56.030000000 Europe/Paris' from dual
  union all
  select 5, timestamp '2019-03-20 00:37:56.030000000 Europe/London' from dual
)
select * 
from MY_TABLE 
where MY_TIMESTAMP >= timestamp '2019-03-19 00:00:00 Europe/London'
and   MY_TIMESTAMP <  timestamp '2019-03-20 00:00:00 Europe/London'
/
        ID MY_TIMESTAMP                                      
---------- --------------------------------------------------
         2 2019-03-19 00:37:56.030000000 EUROPE/LONDON       
         3 2019-03-19 01:00:00.000000000 EUROPE/PARIS        
         4 2019-03-20 00:37:56.030000000 EUROPE/PARIS        

第一个样品行被排除在外，因为巴黎的00:37仍然是伦敦的前一天。第二和第三是因为它们都在当天的凌晨 - 第三排只是刮擦。第四行是出于相同的原因，第一个排被排除在外 - 明天00:37明天仍来自伦敦。第五被排除在外，因为它是在伦敦的午夜之后。