带时区的Oracle DateTime查询

Question

我有一个直接使用ADO.NET的SQL Builder库。我有一种方法可以创建带有大于或等于运算符的选择查询，如：

select *
from book
where book.date_created >= {some date}

我的问题是，{某个日期}将始终在UTC时区，但它被与book.date_created列进行比较，后者是TIMESTAMP（6）WITH time zone列，不会在UTC时区。

我可以执行查询，但由于时区比较，我的结果不正确。我的查询是针对date_created>=x的所有书籍，但返回的一些结果不大于x，因为减去时区的5小时后，它们现在小于x。返回的IDataRecord DateTime字段使用DateTime.SpecifyKind（）转换为UTC

我可以形成我的查询，以便它解释在UTC时区创建的book.date_created吗？

注意：虽然我很想更改我的Oracle DB列，使其不指定时区，但我不能更改表结构

编辑：目前，｛some date｝是一个SQL参数。它的支持数据类型是以UTC为时区的DateTime。作为一个参数，它是一个带有TZ的时间戳。参数的值是一个DateTime，其类型也指定为UTC。

更新：这个问题似乎与我在IDataRecord中的结果集有关。当我关闭DateTimes时，我会使用DateTime.SpecifyKind（）将它们置于UTC模式。问题是，日期时间显示为DateTimeKind.Unspecified。当从Unspecifized转换为UTC时，它只是删除时区并声明它是UTC，而不更改基本值。我不知道如何将IDataRecord拉入TimeZone值。

Answer 1

您需要使用FROM_TZ函数将TIMESTAMP转换为TIMESTAMP WITH TIME ZONE。例如，如果您知道您的变量是UTC时间（+0:00）：

SELECT * 
  FROM book 
 WHERE date_created >= from_tz(<timestamp>, '+0:00');

下面是一个示例脚本，它显示了您所描述的行为（您的本地时区应该设置为+1:00）：

CREATE TABLE t (tz TIMESTAMP(6) WITH TIME ZONE);
INSERT INTO t VALUES 
(to_timestamp_tz('20000101 00:00:00 +1:00','yyyymmdd hh24:mi:ss tzh:tzm'));
INSERT INTO t VALUES 
(to_timestamp_tz('20000101 00:00:00 -1:00','yyyymmdd hh24:mi:ss tzh:tzm'));
-- This will return two values instead of one
SELECT * 
  FROM t 
 WHERE tz >= to_timestamp('20000101 00:00:00', 'yyyymmdd hh24:mi:ss');
-- This query will return only one row
SELECT * 
  FROM t 
 WHERE tz >= from_tz (to_timestamp('20000101 00:00:00', 
                                   'yyyymmdd hh24:mi:ss'), '+0:00');

Answer 2

下面的链接将对您有所帮助
日期时间数据类型和时区支持
TIMESTAMP WITH TIME ZONE数据类型
在Oracle中编写时区感知代码
ORACLE时区摘要
Oracle日期和时间数据类型