Convert COEP TimeStamp (decimal) value to Date and Time in SAP HANA

I assume most of the people here have seen the TIMESTAMP field in COEP or COSS table. These values are decimal and no one can interpret by looking at the value. For example if I give you TIMESTAMP value like this

4.681.764.820.000
4.681.980.000.000
4.681.983.120.000
4.681.996.310.000
4.682.012.800.000
4.682.072.590.000

Can you able to deduce the correct date and time by looking at these values easily, no right? It is very difficult to identify the hidden date and time because these values are stored as a decimal. In SAP we have standard FM which can convert this decimal timestamp into Date and Time separately.

FM – RKE_TIMESTAMP_CONVERT_OUTPUT

TIMESTAMP : 4.681.764.820.000

DATE: 01.11.2004

TIME:18:01:22

This facility is limited to SAP only but if someone wants to explore the similar behavior in Suite on HANA then there is no standard methods or functions available which can allow us to extract Date and Time directly from the decimal timestamp. In my HANA solution I achieved the same functionality using script and graphical calculation view.

SQL Script –

What i basically did is to convert the ABAP  logic of FM “RKE_TIMESTAMP_CONVERT_OUTPUT” into SQL.

FM “RKE_TIMESTAMP_CONVERT_OUTPUT” source code –

DATA: REL_DATE            LIKE SY-DATUM  VALUE ‘19900101’, ” Bezugsdatum
SECONDS_PER_DAY(8)  TYPE P         VALUE 86400,
SECONDS_PER_HOUR(8) TYPE P         VALUE 3600,
DAYS_MAX(8)         TYPE P         VALUE 24853.

I_TZONE = 0

TIMESTMP = ( I_TIMESTMP DIV 10000 ) + I_TZONE.
IF NOT I_DAYST IS INITIAL.
TIMESTMP = TIMESTMP + SECONDS_PER_HOUR.
ENDIF.
E_TIME = TIMESTMP MOD SECONDS_PER_DAY.
E_DATE = TIMESTMP DIV SECONDS_PER_DAY + REL_DATE.

HANA Calculation View #1 (Script based) –

/********* Begin Procedure Script ************/
BEGIN

/**************Extract Date Part********************************/
LT_TMP = SELECT top 100 BELNR AS DOCUMENT,TIMESTMP AS TIMESTMP,
UTCTOLOCAL(ADD_DAYS(TO_DATE (‘1990-01-01’, ‘YYYY-MM-DD’),cast((cast(timestmp/10000 as int)+86400)/86400 as int )) ,’EST’) AS ZTIMESTAMP,

/**********Extract Time Part in HH, MI, SS and then concatenate************/
CAST(MOD(CAST(CAST(TIMESTMP/10000 AS INT ) AS NVARCHAR),86400)/3600 AS INT) AS HOUR2,
CAST(MOD(CAST(MOD(CAST(CAST(TIMESTMP/10000 AS INT ) AS NVARCHAR),86400) AS INT),3600)/60 AS INT) AS MINUTE2,
MOD(MOD(CAST(MOD(CAST(CAST(TIMESTMP/10000 AS INT ) AS NVARCHAR),86400) AS INT),3600),60) AS SECOND2
FROM “SAPRDL”.”COEP”;
LT_TMP2 = SELECT DOCUMENT,TIMESTMP,ZTIMESTAMP,
HOUR2,
MINUTE2
,SECOND2,CONCAT(HOUR2,CONCAT(‘:’,CONCAT(MINUTE2,CONCAT(‘:’,SECOND2)))) AS TOTAL_TIME FROM :LT_TMP;
var_out = SELECT DOCUMENT,TIMESTMP,ZTIMESTAMP,HOUR2,MINUTE2,SECOND2,TOTAL_TIME FROM :LT_TMP2;

END /********* End Procedure Script ************/

Now come back to Time. If you look at the script you can find 3 alias – HOUR, MINUTE, SECOND and concatenating those 3 values to make complete Time like this HH:MI:SS

I created another graphical calculation view where I called script view in one projection. There I created a calculated column “Date” where used this function

date(“ZTIMESTAMP”)

Which gets the Date part only. If you look at the value of ZTIMESTAMP, it is appearing as “Jan 10, 2006 7:00:00.0 PM”. Which when called inside date() returns value

date(Jan 10, 2006 7:00:00.0 PM) = Jan 10, 2006.

In this final calculation view you can find individual column for Date and Time.

This is something took some time to figure it out how to get the date and time separately and wanted to share my effort to others who are struggling to get the correct values from decimal timestamp. Hope you will like the post.