Skip to main content
NagendraKumar
NagendraKumar
Silver ⭐️⭐️⭐️
August 30, 2024
Solved

Convert the Unix time to date time using QueryRecord

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

 

    SAMSAL
    SAMSALAnswer
    Platinum ⭐️⭐️⭐️
    September 3, 2024

    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 in thread

    4 replies

    SAMSAL
    SAMSAL
    Platinum ⭐️⭐️⭐️
    August 31, 2024

    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

    Translate
      NagendraKumar
      NagendraKumarAuthor
      Silver ⭐️⭐️⭐️
      September 3, 2024

      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!

      Translate
        SAMSAL
        SAMSALAnswer
        Platinum ⭐️⭐️⭐️
        September 3, 2024

        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.

         

        Translate
          NagendraKumar
          NagendraKumarAuthor
          Silver ⭐️⭐️⭐️
          September 12, 2024

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

          Translate
            Terms & ConditionsAccessibility statement