This post is the work of Biman Day Sarkar. Thanks to him for sharing this with the wider Oracle integration community.
Convert UTC to Local time zone using Oracle Integration
Business Requirements:
Oracle Integration provides date and time data in UTC format, which is the standard time format used across different systems and platforms. But UTC timestamps are not easily understandable by most users or operators unless they are familiar with time zone offsets. Troubleshooting and debugging can be slower if team members constantly must convert times to local zones. This is particularly challenging for countries that observe daylight savings time. If the logs contain UTC timestamps, you lose the context of local time where events occurred. Thus, many customers prefer to maintain their local time zone while doing any kind of auditing. As a result, developers and system administrators working in a single region benefit from not having to convert from UTC every time they read logs. This blog will help you convert UTC to local time zones around the world while taking daylight saving time into consideration.
Technical Approach:
There are two approaches to handle this depending on the situation:
1. Time Zone Handling without Daylight Saving Time:
Those countries do not follow daylight savings time; for them, approaches are quite straight forward. For examples anyone can create one variable in assign activity & used below expression to get IST from UTC. You also can get desired format using below expressions.
string(fn:adjust-dateTime-to-timezone(fn:current-dateTime(), xsd:dayTimeDuration(‘PT5H30M’)))
OR
xp20:format-dateTime(string(fn:current-dateTime()+xsd:dayTimeDuration(‘PT5H30M’)),'[Y0001]-[M01]-[D01] [H01]:[m01]:[s01]’)
Also, build-in DB functions can be used in those scenarios where the current date needs to be retrieved from the database. Sharing a sample query, which can be executed in any Oracle database to get time zone across the globe. You may refer to https://timezonedb.com/time-zones to get the country code to use in below SQL script.
to_char(systimestamp AT TIME ZONE ‘Asia/Kolkata’, ‘YYYY-MM-DD HH24:MI:SS’)
2. Time Zone Handling with Daylight Saving Time:
For those countries that follow daylight savings, the calculation is not straight forward. Oracle Integration have published a recipe “Oracle Integration — REST | Convert UTC to Local Time” that will help to convert UTC to local time zone worldwide. This recipe will work for those countries as well as those who don’t follow daylight saving. 400+ time zones has been collected across the world, and out of them 140+ time zones follow DST. To make it functional, a variety of complex calculations with in Oracle Integration XSLT & lookups has been performed.
First, an Excel spreadsheet was created where all 400+ time zones have been captured. Where a few crucial fields were kept up to date, assisting us in figuring out the start and end dates of Day Light Savings Time and converting UTC to local time zone. All these field has been maintained in a lookup, which can be added to or altered later if Day Light Saving Time changes for any country. Based on below data available in lookup, complex XSLT logic has been generated to calculate local time zone using Oracle Integration Cloud. The attributes details are as below:
| Key |
Description |
Example |
| TZ_identifier |
The key to fetch all the remaining values from the lookup. |
Australia/Melbourne |
| Country |
Country for the time zone mentioned in TZ_identifier |
Australia |
| SDT |
Time different from UTC during Standard time |
+10:00 |
| DST |
Time different from UTC |
+11:00 |
|
|
during day light savings time |
|
| DST_Flag |
If time zone follows daylight savings time |
Y/N |
| DST_Start_Month / DST_End_Month |
Start / End of Daylight-Savings |
09, 10, 11, 12 |
|
|
months in Number |
|
| DST_Start_Day / DST_End_Day |
Start / End of Daylight-Savings |
Sunday, Monday |
|
|
Day |
|
| DST_Start_Day_Series / DST_End_Day_Series |
Daylight-Savings Start / End Day |
1, 2, 3 OR |
|
|
series of Month |
-1 (In case last Saturday, |
|
|
|
Sunday etc of a month) |
| DST_Start_Time / DST_End_Time |
Daylight-Savings Start / End Time |
02:00:00 |
Execution:
To get this recipe, navigate to Oracle Integration 3 home page, scroll to the Accelerators & Recipes section, and search with time zone. Select the package Oracle Integration — REST | Convert UTC to Local Time, and then click install. Once the Recipe has been installed, configure the REST connection as per recipe documentation and activate.
As per the JSON payload specified in the Oracle documentation, you can test your recipe. To get TZ identifier for your customer you may refer below link, or you may open the lookup ORCL-OIC_WORLD_TIMEZONE_LOOKUP in the recipe where time zone across the world had been consolidated.
https://docs.oracle.com/cd/B13866_04/webconf.904/b10877/timezone.htm
To test the integration, you need to pass the input datetime in UTC along with the time zone in required JSON format. After successful execution, integration will return source time, day light saving start date time, end date time & converted local time. In case the country doesn’t not follow day light savings then only returns source time & converted local time. Same integration can be reused from other integration or project to convert UTC time zone to local time zone.
Sample Input:
{
“inputDateInUTC”: “2024-06-28T10:00:00.000Z”,
“timeZone”: “Australia/Melbourne”
}
Sample Output (countries with day light savings):
{
“sourceTime” : “2024-06-28T10:00:00.000Z”,
“dstStartDate” : “2024-10-06T02:00:00Z”,
“dstEndDate” : “2024-04-07T03:00:00Z”,
“dstTime” : “2024-06-28T20:00:00+10:00”
}
Output field details as follows:
| Key |
Description |
Example |
| sourceTime |
Input Date time in UTC |
2024-06-28T10:00:00.000Z |
| dstStartDate |
Start date of the daylight savings of the time zone provided as input. |
2024-10-06T02:00:00Z |
| dstEndDate |
End date of the daylight savings of the time zone provided as input. |
2024-04-07T03:00:00Z |
| dstTime |
Converted date time considering daylight savings of the time zone provided as input. |
2024-06-28T20:00:00+10:00 |
Sample Output (countries without day light savings):
{
“sourceTime” : “2024-06-28T10:00:00.000Z”,
“dstTime” : “2024-06-28T20:00:00+10:00”
}
Conclusion/Key Takeaways:
This blog will be useful for any calculations that require us to consider the local timestamp while considering all the points listed below:
- Global daylight-saving time computation using a single, standard integration.
- To compute daylight saving time, there is no need for an external API, JavaScript, or database call, which will enhance production performance.
- Future modifications
Customer Benefits:
- Developers don’t have to write custom logic for each time zone conversion.
- Prevents errors from varying time zone databases or inconsistent daylight-saving time (DST) handling.
- Reduces the risk of bugs, especially around DST changes.
- Logs can store time in local time zone to users—improving both technical debugging and user support.