当数据类型为TIMESTAMP和TIMEZONE时，如何获取前一小时的值

Question

因此，如果满足条件，我有一些逻辑将尝试获取与前一小时相关的值(Value(。HOUR列是TIMESTAMP和TIME ZONE列。我想我可以运行以下查询，但得到了一个ORA-00932不一致的数据类型：预期的TIMESTAMP WITH TIME ZONE got NUMBER错误。是否有某种转换函数需要添加到"带时区的时间戳"值中？

以下是我的查询代码：

SELECT MAX(VALUE)
FROM VALUE V
WHERE CODE = 'HI'
AND HR = '15-JAN-17 05.00.00.000000000 AM' - (1/24);

提前谢谢。

Answer 1

'15-JAN-17 05.00.00.000000000 AM'是一个字符串，而不是时间戳。你可以按照@D-Shih的建议将其转换为时间戳(没有时区(，但你应该指定格式掩码和日期语言，而不是依赖NLS设置：

AND HR = to_timestamp('15-JAN-17 05.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM',
'NLS_DATE_LANGUAGE=ENGLISH') - (1/24);

或者如果它是一个固定值(可能不是，或者你可以改变它的字面意思(：

AND HR = timestamp '2017-01-15 05:00:00' - (1/24);

从时间戳中减去天数会得到一个日期结果，所以你可能真的想这样做：

AND HR = timestamp '2017-01-15 05:00:00' - interval '1' hour;

它现在保留为时间戳，但您没有时区信息。如果你知道时区，你可以将其包含在字符串文字和格式掩码中，或者包含在时间戳文字中，例如：

AND HR = timestamp '2017-01-15 05:00:00 America/Los_Angeles' - (1/24);

或者从您的原始字符串中，如果您只需要使用这些，则可以使用from_tz():

AND HR = from_tz(to_timestamp('15-JAN-17 05.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM',
'NLS_DATE_LANGUAGE=ENGLISH'), 'America/Los_Angeles') - interval '1' hour;

最后做间隔减法应该意味着它可以正确处理DST。

各种转换的演示，从您的字符串值开始：

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF1';
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF1 TZR TZD';
select
to_timestamp('15-JAN-17 05.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM',
'NLS_DATE_LANGUAGE=ENGLISH') as a_timestamp,
to_timestamp('15-JAN-17 05.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM',
'NLS_DATE_LANGUAGE=ENGLISH') - (1/24) as b_date,
to_timestamp('15-JAN-17 05.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM',
'NLS_DATE_LANGUAGE=ENGLISH') - interval '1' hour as c_timestamp,
from_tz(to_timestamp('15-JAN-17 05.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM',
'NLS_DATE_LANGUAGE=ENGLISH'), 'America/Los_Angeles') - interval '1' hour as d_timestamp_tz
from dual;
A_TIMESTAMP           B_DATE              C_TIMESTAMP           D_TIMESTAMP_TZ                               
--------------------- ------------------- --------------------- ---------------------------------------------
2017-01-15 05:00:00.0 2017-01-15 04:00:00 2017-01-15 04:00:00.0 2017-01-15 04:00:00.0 AMERICA/LOS_ANGELES PST

Answer 2

使用TO_TIMESTAMP让'15-JAN-17 05.00.00.000000000 AM'到datetime，然后减去一小时。

SELECT MAX(VALUE)
FROM VALUE V
WHERE CODE = 'HI'
AND HR =  TO_TIMESTAMP('15-JAN-17 05.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM') - (1/24);