Oracle Spool file via CMD command deliver more Data than expected

i have a Oracle Table "Sales" with columns ID,Sales,TIMESTAMP. Data looks like this:

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

i created a Talend Job to execute a SQL Spool file in CMD mode to export a Query into csv. The Spoolfile look like this:

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')

when the TalendJob runs the Query on CMD mode, it gives me more data than expected with the Data to '2018-08-25 01:00:00'.

when i execute the SQL Query on Oracle Server manually, it gives correct Data to '2018-08-25 00:00:00'

==> Query on CMD on Talend give 1 hours of Data more than expected.

i don't really understand why that Problem happens. My assumption is the Problem Timestamp in the Query "'2018-08-25 00:00:00.0000000'". this Timestamp has no time zone. but i am not sure.

can you please help me with this Problem? Thankyou.

Solution

The manual query and the Talend query seem to be running in sessions with different time zones.

You aren't specifying a time zone in your fixed value, despite having TZH:TZM in the format model; and in fact you can't with 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

because that function gives you a plain timestamp:

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

When you use that plain timestamp in a comparison with your table column, which is a timestamp with time zone, there is an implicit conversion into the session time zone. You can see the effect that has by manually setting it:

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

So, to be getting different data from your two sessions, that comparison is using a different value, therefore the session time zones must be different.

The simple fix is to specify the time zone explicitly in your fixed value, but you need a different function to avoid the error seen earlier; and preferably with a region instead of an offset to allow for daylight savings (assuming the values in your table are region-based too):

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

or you could use a timestamp literal:

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

which gets the same value.

i haved tried to format the time zone in the Query with to_timestamp_tz(substr('2018-08-25 00:00:00.0000000'),1,25), 'YYYY-MM-DD HH24:mi:ss.ff6 TZH:TZM' at time zone 'berlin/europe') as input_timestamp but it stills gives me more data than expected.

Ignoring the odd substr() which just strips the last two zeros off what is already a fixed string, if you do:

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;

you get (with my session still on New York time for greater effect)

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

The time zone is now what you expected, but the time is wrong. You have much the same problem as before. You're still converting the fixed value with no time zone supplied into a timestamp with time zone, so it's implicitly using the session time zone:

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

and then the at timezone 'Europe/Berlin' just gives that exact same point in universal time - midnight in New York, which is 04:00 UTC - but in Berlin local time, which is 06:00. It's the same point of time, just viewed from different places/time zone.

Again, you just need to specify the time zone for the fixed time you're using for the comparison - as timestamp '2018-08-25 00:00:00.0 Europe/Berlin'.