Conversion of Unix Time Stamp to Normal Time Stamp in Talend

 

Epoch Time stamp: Epoch, also known as Unix timestamp, is the number of seconds (not milliseconds!) that have elapsed since 1 January 1970 00:00:00 GMT (1970-01-01 00:00:00 GMT).

Example: 1597221727

Normal Time stamp: The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision.

Example: 12-08-2020 14:12:45

 

Conversion of Epoch Time Stamp to Normal timestamp:

To convert Epoch timestamp to Normal timestamp in Talend, we use following expression

Local Time: new java.util.Date((long)(row1.unixdate*1000))

GMT: new java.util.Date((long)((row1.unixdate-19800))*1000))

As JavaScript works in milliseconds, it is necessary to convert the time into milliseconds by multiplying it by 1000 before converting it.   

JOB:

 

Procedure:

1.     Create a .txt file with the following Unix Time stamps

unixdate

1356854

1356854

1330578363

1557187200

2.Create a Job named job_unixtonormal, composed of a tFileInputdelimited, a tMap and a tLogRow component linked by two main rows.

3.Double click tFileInputdelimited to open its Component view and fill the File name/Stream field with path of the .txt file you created for example, "C:/folder/in.txt".

4.Click the ellipsis button (...) to edit the schema of the component and add a new column named normaldate.

Note: The data entered can be read with double, float or long type.

5.Double click tMap to configure it, drag and drop the unixDate column of the input table to the output table.

6.Enter newjava.util.Date((long)((row1.unixdate-19800))*1000) into the expression box of the output table column.

7.Change the Type of the data to Date in the Schema editor and enter the following Date pattern: "dd-MM-yyyy HH:mm:ss".You should have the following schema:

8.     Execute the Job. The following output will be displayed in the console.

Output: