UTC to local time conversion with DST

[LeeFung]

I am using BODS with a HANA HDB data store. For UTC to Local , or local to UTC datetime value conversion, shall I use a generic BODS function, or call a function from the HDB? ALSO I needed to make sure that the DST periods are set correctly, which I hope the function called handles correctly itself?

Thanks!

[yogananda]

It is recommended to use the HANA HDB specific functions to convert between UTC and Local datetime values, as they are designed to handle Daylight Savings Time (DST) correctly. The BODS generic functions may not handle DST correctly.

[yogananda]

The best approach for converting UTC to Local or local to UTC datetime value conversion is to call a function from the HDB. The HDB has built-in functions that can be used to easily convert date/time values from one format to another. The DST periods should be taken into account when calling the function and should be handled correctly by the function itself.

[LeeFung]

Thanks Yogananda... what functions from HDB shall I call? I am not sure if from BODS sufficient privileges are given to call functions from HDB...

[jmuiruri · Accepted solution]

Greetings lifengwu,

To convert UTC to local time you can either use SAP Data Services utc_to_local() function which you can easily insert in the column mapping.

To use the HANA UTCTOLOCAL() function you would need to use SQL transform or sql() function. This is because unlike SAP Data Services utc_to_local(), HANA's UTCTOLOCAL() function will not work on column mapping.

print(' HANA UTCTOLOCAL Function');
print(' HANA UTCTOLOCAL Function: ' || sql('HANA','SELECT UTCTOLOCAL (TO_TIMESTAMP(\'2012-01-01 01:03:10\', \'YYYY-MM-DD HH24:MI:SS\'), \'EST\') "utctolocal" FROM DUMMY;'));
print(' BODS UTC_TO_LOCAL Function ');
print(' BODS UTC_TO_LOCAL Function: ' || utc_to_local('2012-01-01 01:03:10', 'UTC-5:00') );

Best Regards,

Joseph

[LeeFung]

I am having some challenge calling the HDB function UTCTOLOCAL from within BODS... BODS simply can't find the function in the datastore.... any tutorial I can go through?

joseph_muiruri

yoganandamuthaiah

[LeeFung]

Thanks

!

It's a pity the BODS utc_to_local() function does not take care of DST! am I right?

[former_member1085868]

I need this answer too. I hope someone will please help.