adjusting timezone

Just to demonstrate the output value using date functions in CQ

1. DateTime Object Conversion to String with Format

CREATE OR REPLACE CQ TransformTxnDate
INSERT INTO testDateStream
SELECT
TO_STRING(TO_DATE(META(x,'TimeStamp')),"yyyy-MM-dd HH:mm:ss.SSS") as ProcessedTime
FROM OracleCDCData x;

W (admin) > select * from testDateStream;
Processing - select * from testDateStream
[
  ProcessedTime = 2018-05-31 13:29:25.000
]

TO_STRING(TO_DATE(META(x,'TimeStamp')),"yyyy/MM/dd HH:mm:ss") as 

W (admin) > select * from testDateStream;
Processing - select * from testDateStream
[
  ProcessedTime = 2018/05/31 13:31:20
]

You might benefit by changing the java timezone property of Striim to UTC so that it is timezone agnostic

add a line in ./bin/server.sh as a JVM property
e.g.,
-Duser.timezone="UTC" \

Snippet of the changes

# Run the Striim server
#
${JAVA} \
    $JVM_DEBUG_OPTS \
    $GC_SETTINGS \
    -Dstriim.modules.path="$WA_HOME/modules" \
    -Duser.timezone="UTC" \
    -Djava.system.class.loader="com.webaction.classloading.StriimClassLoader" \
 

Striim service needs to be modified post the changes. All the logs etc associated with striim will change to UTC as well just as an fyi.

Thanks,

Rajesh

I suspect that “b” would solve the issue, but I was looking for a way without involving Striim administrator. a would only surface the issue, not solve it as far as I understand?

I could make a multipart condition to check whether summer time is observed or not at particular datetime and adjust the number of hours I subtract from the parsed timestamp. Is it the best I can do?

You could also split the processing up into two Continuous Queries (CQs): one to identify and filter records with a timestamp in summer, and another CQ on the output stream of aforementioned query to transform to UTC. 

that would extract the summer timestamp logic to a separate query, outside MODIFY

but the logic itself would still be fairly complex, especially given that different countries observe summer time at different periods