Convert UTC interaction timestamp to EST

[SAPSupport]

Hello SAP,

Is it possible to convert the Custom CDS Views reports from UTC to EST?

Thank you!

------------------------------------------------------------------------------------------------------------------------------------------------
Learn more about the SAP Support user and program here.

[SAPSupport · Accepted solution]

Dear Customer,

The system date/time is set to UTC as it would be used globally. The reports based on CDS view they use default of today’s date which shows UTC date. However, your requirement can be achieved following the recommendation below:

1. Create a custom cube (via the “Custom CDS Views” App) which exposes fields of data type DATS/TIMS (which can be derived from a timestamp via the below mentioned functions).
a. Create calculated fields with functions:
TSTMP_TO_DATS(tstmp,tzone,clnt,on_error)
and TSTMP_TO_TIMS(tstmp,tzone,clnt,on_error)
or DATS_TIMS_TO_TSTMP(date,time,tzone,clnt,on_error)b.

b. You can also create a calculated field with session variable("System Date") - Supported by Custom CDS View

2. Afterwards you can define a custom query based on this custom cube

Please note that the above functions are not supported by Custom CDS View but since calculated fields can also be created using the free editor, you might try to have these functions in your CDS view and see if they work.

List of supported functions can be found here
Documentation on how to create a calculated field can be found here

Thank you!

[Tayane]

Hi Titto,

Please try the following:

dats_tims_to_tstmp( (TSTMP_TO_DATS(YY1_C_LMR_SAC_003_05.InteractionTimeStampUTC, 'EST', $session.client, 'NULL')),
(TSTMP_TO_TIMS(YY1_C_LMR_SAC_003_05.InteractionTimeStampUTC, 'EST', $session.client, 'NULL')),
abap_system_timezone( $session.client,'NULL'),
$session.client,
'NULL' )

Best regards,

Tayane.

[former_member823945]

Hi Tayane,

Thank you so much for the information that you provided.
We tried creating a custom field 'Test3' using the formula below:

dats_tims_to_tstmp( YY1_C_LMR_SAC_003_05.InteractionUTCDate,

YY1_C_LMR_SAC_003_05.InteractionUTCTime,

'EST',

$session.client,

'NULL' )

However, it looks like the calculation is adding 4 hours instead of subtracting 4 hours as per UTC to EDT/EST conversion.

I also tried inserting a comment on the community forum that you created, but it is giving me login issues. Can you please check this and let me know if I am missing something. Or even better if we can schedule a short call with you. Thank you so much in advance!

Attaching screenshot for reference.

Best regards