What is Unix timestamp?

Unix time stamp also known as POSIX time or epoch time is a way to track time for the number of seconds that have elapsed since January 1, 1970 at UTC. The Unix time stamp is the number of seconds between a particular date and the Unix Epoch. The Unix systems store Unix timestamps as a 32-bit integer, but once it reaches the year 2038, January 19 the time overflows and causes problems.

What happens, when the date reaches January 19, 2038?

On January 19, 2038, the Unix time stamp will terminate to work due to 32-bit overflow. To overcome this issue the applications need to adopt a new convention or they should migrate the system to 64-bit.

How to convert the human date to a Unix timestamp?

Following is the process of converting into a Unix timestamp:

Difference of the years: 2017-1970 = 47 years
Hours per day: 24
Minutes per hour: 60
Seconds per minute: 60
Now, 47 years*365 days = 17155 (this is up to only 01-01-2017) + (47/4 i.e, to have the count of leap year days) +
the days up to the current day (eg: my date is 15-03-2017) so the difference is = 74 days.
Now, the total will be similar to 17155+11+74 = 17240
Finally, we will get the human date to Unix epoch.
Difference of years including the days * Hours per day * minutes per hour * seconds per minute
For example = 17240 * 24 * 60 * 60  = 1489536000 (i.e, 15-03-2017)

Step by Step conversion process for Unix epoch to human date in Datastage:

Step 1:

Here, we are taking the ODBC connector as my source to read the data from the database. The table contains one column with unix epoch.

Below is a snapshot for the source data with the unix epoch column highlighted:

The below snapshot is the columns defined:

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

Step 2:

We are using the transformer stage to write the derivation to convert the Unix epoch to a Human date

Now, it is connected to the transformer stage as below snapshot,

Below is a snapshot of the stage variables used in the job,

The derivation used to convert the unix epoch to human date is

DateFromDaysSince(AsInteger(StageVar/24/60/60),StringToDate (“1970-01-01″,”%yyyy-%mm-%dd”))

The output columns for the same are shown in the snapshot below:

Step 3: Loading the data into sequential file – target

The data below in the snapshot shows the conversion of Unix epoch to the human date

The below is the Design for the complete job