时区结果与systimestamp不同。

Question

我有一个Oracle表，它包含以下列定义：

    COLUMN_NAME : RESERVATIONDATE
    DATA_TYPE   : TIMESTAMP(6)
    NULLABLE    : Yes
    DATA_DEFAULT: null

然后，在Java内部执行以下命令：

    insert into my_table (col1, col2, reservationdate) values (:np1, :np2, systimestamp)

然后在其他地方执行以下命令，目的是返回不到X秒前添加的行。

    select * from my_table where reservationDate >= systimestamp - NUMTODSINTERVAL( :seconds, 'SECOND' )

但是，尽管预订日期晚于所标识的点，但不返回任何行。

因此，我还从同一个应用程序中运行了以下命令：

    select col1,
           col2,
           reservationdate,
           systimestamp as b,
           systimestamp - NUMTODSINTERVAL( 5, 'SECOND' ) as c
    from my_table

它提供了以下输出：

    col1: value1
    col2: value2
    reservationdate:2017-06-14 14:31:00.746173
    b              :2017-06-14 15:31:00.905617
    c              :2017-06-14 15:30:55.905617

请注意，b和c返回的值基本上比reservationdate提前一个小时。

在运行在同一台机器上的SQL上运行的请求与Java应用程序提供的值相同：

    reservationdate:14-JUN-17 02.31.00.746173000 PM
    b              :14-JUN-17 02.58.32.863300000 PM +00:00
    c              :14-JUN-17 02.58.27.863300000 PM +00:00

Oracle正在一个虚拟机上运行，其中Unix date的输出是：

    Wed Jun 14 14:18:11 UTC 2017

在Java运行的机器上：

    Wed Jun 14 15:21:24 BST 2017

显然，这是一个时区问题，但我不知道它是从哪里来的。毕竟，我一直在使用systimestamp，目的是在数据库服务器上执行所有时间戳计算。

我的请求通过Spring的NamedParameterJdbcTemplate传递，我使用的是OracleDatabase11gExpress Edition 11.2.0.2.0。

Answer 1

试一试TIMESTAMP WITH TIMEZONE或TIMESTAMP WITH LOCAL TIMEZONE的简单TIMEZONE.

Answer 2

这不是一个完全有条件的答案，但以下是一些信息：

数据类型TIMESTAMP不存储任何时区信息。当您插入时区时间戳(例如SYSTIMESTAMP)时，时区信息将被切断。

SYSTIMESTAMP在数据库服务器的操作系统时区(在您的示例中为UTC)的时区返回TIMESTAMP WITH TIME ZONE数据类型

每当您将TIMESTAMP (没有时区)转换/强制转换为TIMESTAMP WITH TIME ZONE时，Oracle就会考虑SESSIONTIMEZONE --除非您使用TZ或类似的方法显式地设置时区。

Answer 3

我认为您看到了两件事；当您进行调试查询时，您正在拖动、格式化和显示b和c值，这会导致Java转换时区。但我认为这是一条红鲱鱼，因为这不会直接影响您的原始查询来查找最近更改的行。不过，它可能与Java语言环境有关，因此与主要的查询问题有关。

如果您跟踪最初运行的查询，您应该看到它实际使用的筛选器是：

SYS_EXTRACT_UTC(INTERNAL_FUNCTION(RESERVATIONDATE))
  >=SYS_EXTRACT_UTC(SYSTIMESTAMP(6)-NUMTODSINTERVAL(TO_NUMBER(:SECONDS),'SECOND'))

函数调用的存在是因为“在SELECT FROM操作期间，Oracle将数据从列转换为目标变量的类型。”；因此，列值被转换为带有时区的时间戳，以便与systimestamp进行比较，然后将两个时间戳与区域值进行比较，它们都被转换为UTC。

然后，这可以追溯到@WernfriedDomscheit所说的话。函数以一个datetime_with_timezone值作为其参数，因此当传入一个普通的timestamp时，它将使用SESSIONTIMEZONE隐式转换。您看到的查询结果不同，因为您的Java代码和之间的会话时区不同。

在这两种情况下，您都可以使用select sessiontimezone from dual来查看它们是什么，或者查看时间戳是如何转换的。

您可以通过将系统时间转换回无时区时间戳来避免此问题：

... where reservationDate >= cast(systimestamp as timestamp) - NUMTODSINTERVAL( :seconds, 'SECOND' );

跟踪显示了更简单的：

RESERVATIONDATE
  >=CAST(SYSTIMESTAMP(6) AS timestamp)-NUMTODSINTERVAL(TO_NUMBER(:SECONDS),'SECOND') 

将您的列定义从timestamp更改为timestamp with [local] time zone也是有效的；根据我的跟踪，对于local，向UTC的转换仍然会发生，如果没有本地的话，它就不会发生。这也是一本有趣的读物。

如果您查看如何使用不同的会话时区解析sys_extract_utc()调用，您可以看到差异，使用新插入的行并省略date元素以保持简洁性：

insert into my_table (col1, col2, reservationdate) values (:np1, :np2, systimestamp);

alter session set nls_timestamp_format = 'HH24:MI:SS.FF1';
alter session set nls_timestamp_tz_format = 'HH24:MI:SS.FF1 TZR';

alter session set time_zone = 'Europe/London';

select reservationdate a,
  systimestamp b,
  systimestamp(6)-numtodsinterval(to_number(:seconds),'SECOND') c,
  cast(reservationdate as timestamp with time zone) d,
  sys_extract_utc(reservationdate) e,
  sys_extract_utc(systimestamp) f,
  sys_extract_utc(systimestamp(6)-numtodsinterval(to_number(:seconds),'SECOND')) g
from my_table;

A           B                 C                 D                        E           F           G          
----------- ----------------- ----------------- ------------------------ ----------- ----------- -----------
17:12:13.9  17:12:15.0 +01:00 17:12:10.0 +01:00 17:12:13.9 EUROPE/LONDON 16:12:13.9  16:12:15.0  16:12:10.0 

alter session set time_zone = 'UTC';

select reservationdate a,
  systimestamp(6) b,
  systimestamp(6)-numtodsinterval(to_number(:seconds),'SECOND') c,
  cast(reservationdate as timestamp with time zone) d,
  sys_extract_utc(reservationdate) e,
  sys_extract_utc(systimestamp) f,
  sys_extract_utc(systimestamp(6)-numtodsinterval(to_number(:seconds),'SECOND')) g
from my_table;

A           B                 C                 D              E           F           G          
----------- ----------------- ----------------- -------------- ----------- ----------- -----------
17:12:13.9  17:12:15.4 +01:00 17:12:10.4 +01:00 17:12:13.9 UTC 17:12:13.9  16:12:15.4  16:12:10.4 

我的设置似乎与您的不同--我的数据库服务器在英国时间，但是DBTIMEZONE是+00:00 --尽管如此，注意e列中这两个值之间的差异。在我的例子中，它看起来会发现太多的数据，而不是太少--因为e >= g是真的。在您的例子中，我相信如果您从SQL Developer和Java运行这些代码，您会看到这种差异是相反的，因为您的Java应用程序的会话时区(基于它的区域设置)正以相反的方式导致这种变化。