how to convert the time from epoch or Unix Time in ADF activity

Bommisetty, Rakesh (Hudson IT Consultant) 165 Reputation points
2024-03-25T07:08:56.7+00:00

how to convert the time from epoch or Unix Time in ADF activity like set,lookup etc..

ex: i have one folder in S3 bucket but the folder name is in Unix time or epoch time ex:17908765 i need to convert the folder to datetime/date format how can i achieve?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,548 questions
Accepted answer
  1. AnnuKumari-MSFT 30,751 Reputation points Microsoft Employee
    2024-03-29T10:32:04.13+00:00

    Hi Bommisetty, Rakesh (Hudson IT Consultant) ,

    Unfortunately, it's not possible directly via ADF activities as of now. You need to rely on dataflow derived column transformation to convert EPOCH timestamp using below expression:

    toTimestamp(toInteger(toString(byName('timestamp')))*1000l,'yyyy-MM-dd HH:mm:ss')

    For more details, kindly check this thread: https://stackoverflow.com/questions/59118389/azure-data-factory-mapping-data-flow-epoch-timestamp-to-datetime

    Hope it helps. Kindly accept the answer by clicking on Accept answer button.

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Harishga 3,345 Reputation points Microsoft Vendor
    2024-03-26T07:34:56.2466667+00:00

    Hi @Bommisetty, Rakesh (Hudson IT Consultant)

    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    In Azure Data Factory, you can use the Lookup and DerivedColumn activities to convert Unix time or epoch time to a datetime/date format.

    Firstly, you need to use the Lookup activity to retrieve the folder name from S3 bucket based on the Unix time/epoch time format. Then, you can use the DerivedColumn activity to convert the Unix time/epoch time format to a datetime/date format using the toDate() function.

    Lookup activity 

    • Add a Lookup activity to your ADF pipeline. In the Lookup activity, specify the folder path and file name pattern to retrieve the folder name based on the Unix time/epoch time format. This will retrieve the folder name from the S3 bucket.

    Derived column activity

    • Add a DerivedColumn activity after the Lookup activity. In the DerivedColumn activity, use the toDate() function to convert the Unix time/epoch time to a datetime/date format. 

    Here's an example: expression that you can use in the DerivedColumn activity: toDate(toInteger(substring(folderName, 0, 10))) This expression converts the first 10 characters of the folder name (which represent the Unix time/epoch time) to an integer and then converts it to a datetime/date format using the toDate() function. The toDate() function takes an integer value as input, which represents the number of seconds since the Unix epoch time (January 1, 1970, 00:00:00 UTC). The function returns a datetime value in the format "yyyy-MM-ddTHH:mm:ssZ".

    Finally, you can use the output of the DerivedColumn activity to pick the latest date/time folder from S3 and place it into blob storage. You can use a Copy activity to copy the contents of the latest folder to blob storage. In the Copy activity, specify the source folder path as the S3 bucket folder path with the latest folder name, and the destination folder path as the blob storage container path.

    Reference
    https://learn.microsoft.com/en-us/azure/data-factory/data-flow-date-time-functions
    I hope this information helps you. Let me know if you have any further questions or concerns.