SAP Data Services – Convert epoch to human-readable date

Unix timestamp is one of the best ways to show your reader’s real time no matter where in the world they are. For example, some web services could return date in Unix timestamp format. How to convert it to regular date using SAP Data Services?

Unix time (also known as POSIX time or UNIX Epoch time) is a system for describing a point in time. It is the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC), minus leap seconds.

To convert Unix time to human-readable date format using SAP Data Services, you need only one built-in function num_to_interval() and a little custom code. Create custom function and declare variables as shown below:

$elapsed_ms (double) – UNIX epoch time in milliseconds [Input parameter]

$elapsed_sec (double) – UNIX epoch time in seconds

$epoch (date) – Thursday, 1 January 1970

$human_date (date) – desirable result

#This function converts UNIX Epoch time to Date format
$elapsed_sec = $elapsed_ms/1000;
$epoch = '1970.01.01';
$human_date = $epoch + num_to_interval($elapsed_sec,'S');
return $human_date;

If you want your result in datetime format, change function as shown below, also change $epoch and $human_date type to datetime:

#This function converts UNIX Epoch time to Datetime format
$elapsed_sec = $elapsed_ms/1000;
$epoch = '1970.01.01 00:00:00';
$human_date = $epoch + num_to_interval($elapsed_sec,'S');
return $human_date;

After reading this blog post, I hope you are able to convert UNIX epoch time to “normal” date (or datetime) format using this function in your query transforms. Just apply custom function to desirable column.