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
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
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.
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 ************/
/**************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
LT_TMP2 = SELECT DOCUMENT,TIMESTMP,ZTIMESTAMP,
,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
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.
Would you like to access Full Article?
If you are already a member in this website, Please Click here to loginIf you are not yet a member, Please Click here to Sign up
New NetWeaver Information at SAP.com