正在将时间戳字段转换为正确的时区

Question

我们的数据存储在UTC+00:00时区。

为了进行查询，我们必须将时间戳输入从"欧洲/巴黎"时区转换为UTC+00:00，即欧洲/伦敦。当我们得到这些数据时，将输出时间戳转换为"欧洲/巴黎"时区。

所以我找到的解决方案是使用像cast(VALIDATIONTIME as timestamp) at time zone 'Europe/Paris'这样的"在时区"它可以处理输入，但不能处理输出（当我们获得数据时）。

那么，如何使它为输入和输出工作呢？

示例：

Orders table
ID      VALIDATIONTIME
9986    14/03/16 09:47:29,823000000
6764    20/03/16 12:07:39,453000000

要获得行id 9986，查询将是：

select cast(VALIDATIONTIME as timestamp) at time zone 'Europe/Paris' as validation 
from orders 
where VALIDATIONTIME between cast(TO_TIMESTAMP('14/03/2016 10:45:18', 'DD/MM/YYYY HH24:MI:SS') as timestamp) at time zone 'UTC'
and cast(TO_TIMESTAMP('14/03/2016 10:50:18', 'DD/MM/YYYY HH24:MI:SS') as timestamp) at time zone 'UTC';

但是输出（验证）没有改变！！

Answer 1

at time zone语法将带有时区值的时间戳从其当前区域转换为您指定的区域。当您将其应用于没有时区的纯时间戳时，就像您遵循cast()一样，首先会隐式转换为会话时区。

我想你真的在寻找from_tz()功能：

select VALIDATIONTIME at time zone 'Europe/Paris' as validation 
from orders 
where VALIDATIONTIME between
  FROM_TZ(TO_TIMESTAMP('14/03/2016 10:45:18', 'DD/MM/YYYY HH24:MI:SS'), 'Europe/Paris')
and
  FROM_TZ(TO_TIMESTAMP('14/03/2016 10:50:18', 'DD/MM/YYYY HH24:MI:SS'), 'Europe/Paris');

您可以通过独立查询查看生成的时间；这也将指定在巴黎时区的时间转换为UTC进行比较：

select
  FROM_TZ(TO_TIMESTAMP('14/03/2016 10:45:18', 'DD/MM/YYYY HH24:MI:SS'), 'Europe/Paris')
    as Paris,
  FROM_TZ(TO_TIMESTAMP('14/03/2016 10:45:18', 'DD/MM/YYYY HH24:MI:SS'), 'Europe/Paris')
    at time zone 'UTC' as London
from dual;
PARIS                                      LONDON                                   
------------------------------------------ ------------------------------------------
14-MAR-16 10.45.18.000000000 EUROPE/PARIS  14-MAR-16 09.45.18.000000000 UTC          

（当然，伦敦在夏天不是UTC，但我们现在忽略这个细节；我想你实际上想要的是UTC，而不是伦敦）。

根据validationtime的数据类型和会话时区，您可能需要对其进行更多操作。通过一些简单的数据设置和validationtime作为简单的时间戳（没有时区信息）：

create table orders (id number, validationtime timestamp);
insert into orders (id, validationtime)
values (9986, to_timestamp('14/03/16 09:47:29,823000000', 'DD/MM/RR HH24:MI:SS,FF'));
insert into orders (id, validationtime)
values (6764, to_timestamp('20/03/16 12:07:39,453000000', 'DD/MM/RR HH24:MI:SS,FF'));

如果我的会议在伦敦时间，那么基本查询有效：

alter session set time_zone = 'Europe/London';
select VALIDATIONTIME at time zone 'Europe/Paris' as validation 
from orders 
where VALIDATIONTIME between
  FROM_TZ(TO_TIMESTAMP('14/03/2016 10:45:18', 'DD/MM/YYYY HH24:MI:SS'), 'Europe/Paris')
and
  FROM_TZ(TO_TIMESTAMP('14/03/2016 10:50:18', 'DD/MM/YYYY HH24:MI:SS'), 'Europe/Paris');
VALIDATION                               
------------------------------------------
14-MAR-16 10.47.29.823000000 EUROPE/PARIS 

但如果我把会议时区改为巴黎，那么现在就不会了：

alter session set time_zone = 'Europe/Paris';
select VALIDATIONTIME at time zone 'Europe/Paris' as validation 
from orders 
where VALIDATIONTIME between
  FROM_TZ(TO_TIMESTAMP('14/03/2016 10:45:18', 'DD/MM/YYYY HH24:MI:SS'), 'Europe/Paris')
and
  FROM_TZ(TO_TIMESTAMP('14/03/2016 10:50:18', 'DD/MM/YYYY HH24:MI:SS'), 'Europe/Paris');
no rows selected

因此，您需要指定表中的原始时间戳为UTC，您可以使用相同的函数：

select FROM_TZ(VALIDATIONTIME, 'UTC') at time zone 'Europe/Paris' as validation 
from orders 
where FROM_TZ(VALIDATIONTIME, 'UTC') between
  FROM_TZ(TO_TIMESTAMP('14/03/2016 10:45:18', 'DD/MM/YYYY HH24:MI:SS'), 'Europe/Paris')
and
  FROM_TZ(TO_TIMESTAMP('14/03/2016 10:50:18', 'DD/MM/YYYY HH24:MI:SS'), 'Europe/Paris');
VALIDATION                               
------------------------------------------
14-MAR-16 10.47.29.823000000 EUROPE/PARIS 

本质上，在比较之前，你需要确保你使用的所有日期都在同一区域。另一种方法是强制可变日期为UTC，然后为纯时间戳：

select FROM_TZ(VALIDATIONTIME, 'UTC') at time zone 'Europe/Paris' as validation 
from orders 
where VALIDATIONTIME
between
  cast(FROM_TZ(TO_TIMESTAMP('14/03/2016 10:45:18', 'DD/MM/YYYY HH24:MI:SS'),
    'Europe/Paris') at time zone 'UTC' as timestamp)
and
  cast(FROM_TZ(TO_TIMESTAMP('14/03/2016 10:50:18', 'DD/MM/YYYY HH24:MI:SS'),
    'Europe/Paris') at time zone 'UTC' as timestamp);
VALIDATION                               
------------------------------------------
14-MAR-16 10.47.29.823000000 EUROPE/PARIS 

这看起来更复杂，但由于它不修改where子句中的表列，它将允许仍然使用该列上的任何索引。

在这里，变量字符串被转换为纯时间戳，声明为在巴黎时区，转换为UTC，然后再转换回纯时间戳-因此它的格式和（隐含的）时区与表列数据相同。

Answer 2

Oracle设置:

CREATE TABLE orders (
  ID INT,
  VALIDATIONTIME TIMESTAMP
);
INSERT INTO orders VALUES (
  9968,
  FROM_TZ( TIMESTAMP '2016-03-14 09:47:29.823', 'Europe/Paris' )
    AT TIME ZONE 'UTC'
);
-- Alternate syntax for inserting from TZ:
INSERT INTO orders VALUES (
  6764,
  TO_TIMESTAMP_TZ( '2016-03-20 20:07:39.453 Europe/Paris',
                   'YYYY-MM-DD HH24:MI:SS.FF TZR' )
    AT TIME ZONE 'UTC'
);

或者更简单：

INSERT INTO orders VALUES (
  9968,
  TIMESTAMP '2016-03-14 09:47:29.823+01:00' AT TIME ZONE 'UTC'
);

或：

INSERT INTO orders VALUES (
  6764,
  TIMESTAMP '2016-03-20 20:07:39.453 Europe/Paris' AT TIME ZONE 'UTC'
);

查询1：

SELECT * FROM orders;
  ID VALIDATIONTIME
---- -----------------------------
9986 2016-03-14 08:47:29.823000000
6764 2016-03-20 19:07:39.453000000

（这在表中显示UTC时间）

查询2：

SELECT id,
       CAST( VALIDATIONTIME AS TIMESTAMP WITH TIME ZONE )
         AT TIME ZONE 'Europe/Paris' AS validationtime
FROM   orders
  ID VALIDATIONTIME
---- ------------------------------------------
9986 2016-03-14 09:47:29.823000000 EUROPE/PARIS
6764 2016-03-20 20:07:39.453000000 EUROPE/PARIS