Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Convert the Unix time to date time using QueryRecord

avatar
Rising Star

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>): {}

NagendraKumar_0-1725026832656.png

 

1 ACCEPTED SOLUTION

avatar
Super Guru

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:

SAMSAL_0-1725346972085.png

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:

SAMSAL_1-1725347222813.png

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

Hope that helps.

 

View solution in original post

4 REPLIES 4

avatar
Super Guru

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

avatar
Rising Star

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.

NagendraKumar_0-1725340839021.png

It returned the same output

NagendraKumar_1-1725340917324.png

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 :

NagendraKumar_2-1725341106471.png

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)}: {}

NagendraKumar_3-1725341231875.png

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!

avatar
Super Guru

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:

SAMSAL_0-1725346972085.png

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:

SAMSAL_1-1725347222813.png

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

Hope that helps.

 

avatar
Rising Star

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