Question

adjusting timezone

  • 5 January 2024
  • 5 replies
  • 72 views

Badge +1
  • Known Participant
  • 12 replies

I’d like to convert a timestamp from location specific one (in areas that have summer time change) to UTC. What would be the best way to do it within Striim? I assume it’s doable using Joda API and I can see in the reference that “Striim supports all date functions natively associated with Joda-Time.” but I didn’t find an example how to call those Joda Time functions


5 replies

Badge +1

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
]

 

  1.  

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

Badge +1

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?

Badge +1

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?

Userlevel 1
Badge

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. 

Badge +1

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

Reply