UTC TO LOCAL TIME | Community

UTC TO LOCAL TIME

Hello,

I'm curious if anybody else has already faced the following issue:

For some tables, timestamps are stored in UTC in SAP, although in display mode of transaction timestamp is shown in local time. This leads to UTC timestamps for activities in Celonis and can cause confusion of end users.

I already tried SQL best practise to transfer UTC in local time but standard operations/ local time zones are disabled in Event Collection.

SELECT GETDATE() only provides UTC (Vertica base)
getutcdate() is not supported (Vertica base)
SELECT * FROM sys.time_zone_info is not allowed/missing rights (SQL Server base)

It would be better to avoid hard coded transformations due to summer and winter time in Europe which will lead to maintaining effort.

Does anybody else has an idea how to transfor UTC in local time?

Looking forward to hear your experiences!

Greetings from Germany,

Jasmin

Page 1 / 1

Hi Jasmin,

I have been using the following in a transformation with SRM data. I don't know if it will help you, but you can have a try by applying the desired timestamp pattern:

TO_TIMESTAMP_TZ (I."CREATED_AT", 'YYYYMMDDHHmiSS') at timezone 'Europe/Brussels'

regards

Marc

Hi Jasmin,

I have been using the following in a transformation with SRM data. I don't know if it will help you, but you can have a try by applying the desired timestamp pattern:

TO_TIMESTAMP_TZ (I."CREATED_AT", 'YYYYMMDDHHmiSS') at timezone 'Europe/Brussels'

regards

Marc

Thannk you, Marc!

Are you also using EMS? Because if I try the adjusted formula, I still receive the following error:

Function TO_TIMESTAMP_TZ(timestamp, unknown) does not exist, or permission is denied for TO_TIMESTAMP_TZ(timestamp, unknown)

Thannk you, Marc!

Are you also using EMS? Because if I try the adjusted formula, I still receive the following error:

Function TO_TIMESTAMP_TZ(timestamp, unknown) does not exist, or permission is denied for TO_TIMESTAMP_TZ(timestamp, unknown)

Hi Jasmin,

yes, it is on EMS Cloud platform. But this formula will convert a string to a timestamp ;-)

I just have tried a couple of things with a timestamp as such, and figured out that it first needs to be converted before you can use the formula...

SELECT

EVENTTIME

,TO_TIMESTAMPTZ (TO_CHAR(EVENTTIME), 'YYYY-MM-DD HH:mi:SS') AT TIME ZONE 'Europe/Brussels'

FROM _CEL_P2P_ACTIVITIES

will give the following result as eventtime is considered at UTC by the formula

timezone_conversion

regards

Marc

Thannk you, Marc!

Are you also using EMS? Because if I try the adjusted formula, I still receive the following error:

Function TO_TIMESTAMP_TZ(timestamp, unknown) does not exist, or permission is denied for TO_TIMESTAMP_TZ(timestamp, unknown)

Hi Marc,

thank you so much. It finally worked with the conversion to char first!!

I also discovered that if the column is already saved as timestamp you can also use

SELECT ACTIVITY_EN, EVENTTIME,CAST(_CEL_MDGS_ACTIVITIES."EVENTTIME" AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Berlin' AS DATETIME) AS EVENTTIME2 FROM _CEL_MDGS_ACTIVITIES

Regards,

Jasmin

Thannk you, Marc!

Are you also using EMS? Because if I try the adjusted formula, I still receive the following error:

Function TO_TIMESTAMP_TZ(timestamp, unknown) does not exist, or permission is denied for TO_TIMESTAMP_TZ(timestamp, unknown)

Waouw, thanks for sharing, Jasmin. I didn't think of using CAST for this.

It's great to see that people can learn from each other .

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN