Apache Nifi: substract hours from column value with datetime format

[#1]

Hello!

Input flowFile has column "date_time" with timestamp-millis type 

The task is to update each value of column, by substracting 3 hours from datetime value. In fact I need to transform datetime value from timezone GMT +3 to timezone GMT +0

For example, value "2024-11-24 19:43:17" to be transformed in "2024-11-24 16:43:17"

I tried to use UpdateRecord with following script:

           format(toNumber((/date_time):minus(3600)), "yyyy-MM-dd HH:mm:ss")

And, as expected, transformation failed with exception: "Unrecognized token 'minus'..."

Operation "minus" applied to attributes values. And "date_time" is not an attribute.

As an alternative, I considered next way:

1. SplitText for splitting one FlowFile into N FlowFiles, where N - is the number of records.

2.UpdateAttribute to add new attribute "dateTime" for each of N flowFiles by: 
dateTime <- /date_time

3. UpdateRecord for each of N flowFiles with script: /date_time <- ${dateTime:toNumber:minus(3600):format("yyyy-MM-dd HH:mm:ss")}

4.Merge N flowFiles to one FlowFile

But this way seem to be too complicated. Is there are more straightforward way? ))

[#2 · Accepted solution]

Hi ,

I dont see a function toNumber in the record path syntax , so Im not sure how did you come up with this. It would be helpful next time if you provide the following information:

1- input format.

2- screenshot of the processor configuration causing the error.

As for your problem , the easiest and more efficient way - than splitting records- I can think of is  using the QueryRecrod processor. lets assume you have the following csv input:

id,date_time
1234,2024-11-24 19:43:17
5678,2024-11-24 01:10:10

You can pass the input to the QueryRecord Processor with the following config:

The query above is added as a dynamic property which will expose new relationship with the property name that you can use to get the desired output. The query syntax is the following:

select id,TIMESTAMPADD(HOUR, -3,date_time) as date_time from flowfile

The trick for this to work is how you configure the CSV Reader and Writer to set the expectation on how to parse datetime fields in the reader\writer services:

For the CSVReader, Make sure to set the following:

CSVRecordSetWriter:

Output through Result relationship:

id,date_time
1234,2024-11-24 16:43:17
5678,2024-11-23 22:10:10

Hope that helps. If it does, please accept solution.

Thanks