代码之家 › 专栏 › 技术社区 › phalondon

Oracle Spool文件通过CMD命令传递的数据比预期的多

talend cmd oracle sql

phalondon · 技术社区 · 5 年前

我有一个Oracle表“Sales”,列ID,Sales,TIMESTAMP。数据如下:

ID  Sales TimeStamp
1    30   2018-08-20 00:00:00.989900 +02:00 
1    35   2018-08-21 05:00:00.989900 +02:00
...
1    35   2018-08-27 05:00:00.989900 +02:00

我创建了一个Talend作业,在CMD模式下执行一个SQL Spool文件,将查询导出到csv。后台文件如下所示:

alter session set NLS_TIMESTAMP_TZ_FORMAT ='YYYY-MM-DD HH24:mi:ss.ff6 TZH:TZM';
alter session set NLS_TIMESTAMP_FORMAT ='YYYY-MM-DD HH24:mi:ss.ff6';
alter session set NLS_DATE_FORMAT ='YYYY-MM-DD';
alter session set NLS_NUMERIC_CHARACTERS ='.,';
spool C:/test.csv
SET ECHO OFF
SET ...
SELECT * FROM Sales where timestamp< to_timestamp('2018-08-25 00:00:00.0000000','YYYY-MM-DD HH24:mi:ss:ff66 TZH:TZM')

当我在Oracle服务器上手动执行SQL查询时,它会向“2018-08-25 00:00:00”提供正确的数据

==>在Talend上查询CMD,得到比预期多1小时的数据。

我的假设是查询“2018-08-25 00:00:00.0000000”中的问题时间戳。这个时间戳没有时区。但我不确定。

你能帮我解决这个问题吗?

0 回复 | 直到 5 年前

Alex Poole 5 年前

手动查询和Talend查询似乎在不同时区的会话中运行。

TZH:TZM 在格式模型中;实际上 to_timestamp() :

select to_timestamp('2018-08-25 00:00:00.0000000 +02:00','YYYY-MM-DD HH24:mi:ss:ff6 TZH:TZM')
from dual;

ORA-01821: date format not recognized

alter session set NLS_TIMESTAMP_FORMAT ='YYYY-MM-DD HH24:mi:ss.ff6';
alter session set NLS_TIMESTAMP_TZ_FORMAT ='YYYY-MM-DD HH24:mi:ss.ff6 TZH:TZM';

select to_timestamp('2018-08-25 00:00:00.0000000','YYYY-MM-DD HH24:mi:ss:ff6 TZH:TZM')
  as plain_timestamp
from dual;

PLAIN_TIMESTAMP           
--------------------------
2018-08-25 00:00:00.000000

当您在与表列(带时区的时间戳)比较时使用该纯时间戳时,会隐式转换为会话时区。通过手动设置,可以看到效果:

alter session set time_zone = 'Europe/London';

select cast(
         to_timestamp('2018-08-25 00:00:00.0000000','YYYY-MM-DD HH24:mi:ss:ff6 TZH:TZM')
         as timestamp with time zone
       ) as timestamp_with_session_zone
from dual;

TIMESTAMP_WITH_SESSION_ZONE      
---------------------------------
2018-08-25 00:00:00.000000 +01:00

alter session set time_zone = 'America/New_York';

select cast(
         to_timestamp('2018-08-25 00:00:00.0000000','YYYY-MM-DD HH24:mi:ss:ff6 TZH:TZM')
         as timestamp with time zone
       ) as timestamp_with_session_zone
from dual;

TIMESTAMP_WITH_SESSION_ZONE      
---------------------------------
2018-08-25 00:00:00.000000 -04:00

简单的解决方法是在固定值中显式指定时区,但您需要一个不同的函数来避免前面看到的错误;最好使用一个区域而不是偏移量来允许日光节约(假设表中的值也是基于区域的):

select to_timestamp_tz('2018-08-25 00:00:00.0000000 Europe/Berlin','YYYY-MM-DD HH24:mi:ss:ff6 TZR')
  as timestamp_with_berlin_zone
from dual;

TIMESTAMP_WITH_BERLIN_ZONE       
---------------------------------
2018-08-25 00:00:00.000000 +02:00

或者可以使用时间戳文字:

select timestamp '2018-08-25 00:00:00.0 Europe/Berlin' as timestamp_with_berlin_zone
from dual;

我试图用to_timestamp_z(substr('2018-08-25 00:00:00.0000000'),1,25)、'YYYY-MM-DD HH24:mi:ss.ff6 TZH:TZM'at time zone'berlin/europe')作为输入时间戳来格式化查询中的时区,但它仍然提供了比预期更多的数据。

忽略奇数 substr()

select to_timestamp_tz('2018-08-25 00:00:00.0000000', 'YYYY-MM-DD HH24:mi:ss.ff6 TZH:TZM')
  at time zone 'Europe/Berlin' as timestamp_with_wrong_time
from dual;

你得到(我的会议仍在纽约时间,以取得更大的效果)

TIMESTAMP_WITH_WRONG_TIME        
---------------------------------
2018-08-25 06:00:00.000000 +02:00

时区现在是你所期望的,但是时间是错误的。你和以前有很多相同的问题。您仍在将不带时区的固定值转换为带时区的时间戳,因此它隐式地使用会话时区:

select to_timestamp_tz('2018-08-25 00:00:00.0000000', 'YYYY-MM-DD HH24:mi:ss.ff6 TZH:TZM')
  as timestamp_with_wrong_time
from dual;

TIMESTAMP_WITH_WRONG_TIME        
---------------------------------
2018-08-25 00:00:00.000000 -04:00

然后 at timezone 'Europe/Berlin' 只是给出了和世界时完全相同的时间点——纽约的午夜时间是UTC的04:00——但是柏林当地时间是06:00。这是同一时间点,只是从不同的地点/时区观看。

同样,您只需要指定用于比较的固定时间的时区- timestamp '2018-08-25 00:00:00.0 Europe/Berlin' .