Convert the Unix time to date time using QueryRecord

[#1]

We are working on converting the Unix time into date-time using the QueryRecord processor. We understand that QueryRecord is built with the calcite SQL syntax and tried to use the DATE_FROM_UNIX_DATE function. We are receiving the below error. Please help us resolve this issue with your expertise.

We can achieve this conversion using the update attribute but we wanted this as part of the Query record as we wanted to apply this conversion on the completed dataset.

Query :

SELECT DATE_FROM_UNIX_DATE(1724851471) FROM FLOWFile

Error :

QueryRecord[id=2cc603e4-0191-1000-f3c7-ef830564cf72] Unable to query FlowFile[filename=convertfunctionsample.csv] due to org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Error while preparing statement [SELECT DATE_FROM_UNIX_DATE(1724851471) FROM FLOWFile]
- Caused by: java.sql.SQLException: Error while preparing statement [SELECT DATE_FROM_UNIX_DATE(1724851471) FROM FLOWFile]
- Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to line 1, column 38: No match found for function signature DATE_FROM_UNIX_DATE(<NUMERIC>)
- Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature DATE_FROM_UNIX_DATE(<NUMERIC>): {}

[#2]

Hi @NagendraKumar ,

Im not sure that you can use the function "DATE_FROM_UNIX_DATE"  since according to the sqlcalcite documentation its not a standard function. If I may recommend two approaches to solve this problem:

1- Using Sql Calcite function TIMESTAMPADD:

select TIMESTAMPADD(SECOND, 1724851471,cast('1970-01-01 00:00:00' as timestamp)) mytimestamp from flowfile

2- Using Expression Language:

select '${literal('1724851471'):multiply(1000):format('yyyy-MM-dd HH:mm:ss')}' mytimestamp from flowfile

In both cases you have to be aware of the timezone that the timestamp is converted into I think one uses local while the other uses GMT

Hope that helps.

If it helps please accept the solution.

Thanks

[#3]

Thanks a lot, @SAMSAL  for the technical suggestion!

Please find below our comments on both the proposed solutions,

Solution 1: Using Sql Calcite function TIMESTAMPADD

This method does not perform the conversion. Please refer to the below screenshot for more details and let us know if we are making any mistakes.

It returned the same output

Solution 2: Using Expression Language

This solution works fine when we pass the data directly into the query record. However, we wanted to apply this conversion for the batch of data in the CSV input file. Please refer below for the complete flow

Complete NiF Flow :

Input File :

Id,username,load_date
1,test@test.com,1725000090

Error Message :

QueryRecord[id=2cc603e4-0191-1000-f3c7-ef830564cf72] Unable to query FlowFile[filename=convertfunctionsample.csv] due to org.apache.nifi.attribute.expression.language.exception.AttributeExpressionLanguageException: Invalid Expression: select '${literal(load_date):multiply(1000):format('yyyy-MM-dd HH:mm:ss')}' mytimestamp from flowfile due to Unexpected token 'load_date' at line 1, column 10. Query: ${literal(load_date):multiply(1000):format(yyyy-MM-dd HH:mm:ss)}: {}

Please help us with your expertise if there is any way to pass the column from the flowfile to the Expression language. Thanks in advance!

[#4 · Accepted solution]

Hi,

My Apologies. I think I forgot to mention that in both cases you need to set the Timestamp Format in the CSVRecordWriter to the target format as follows since by default it converts the datetime to epoch time:

The point from the conversion in the QueryRecord is to tell the CSVReader that this is a datetime , however without setting the format in the writer it was converting it back to epoch time as the documentation states:

Setting the format there is critical to get the desired output.

Hope that helps.

[#5]

Thanks a lot, @SAMSAL! The proposed solution worked for us.