OIC - Converting standard DateTime to Unix Time in OIC Using XSLT functions

Use Case

In Oracle Integration Cloud (OIC), there are scenarios where we need to convert a standard DateTime value into Unix time (Epoch time), which represents the number of seconds elapsed since January 1, 1970, UTC. This is commonly required when integrating with APIs that accept timestamps in Unix format.

Solution Steps

To achieve this conversion, we use XPath expressions within OIC, leveraging xsd:dateTime, xp20:format-dateTime, fn:current-dateTime(), and xsd:dayTimeDuration.

Expression Used

floor (((xsd:dateTime (xp20:format-dateTime (fn:current-dateTime(), 

"[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01]Z" ) ) 
- xsd:dateTime ("1970-01-01T00:00:00Z" )) 

div xsd:dayTimeDuration ("PT1S") ))

Modified version:

floor(
(
    fn:current-dateTime()
    - xsd:dateTime("1970-01-01T00:00:00Z")
) div xsd:dayTimeDuration("PT1S")
)

Breakdown of the Expression

1. fn:current-dateTime() → Retrieves the current DateTime in the integration.
2. xp20:format-dateTime(..., "[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01]Z") → Formats it to a standard UTC DateTime format.
3. xsd:dateTime(...) - xsd:dateTime("1970-01-01T00:00:00Z") → Calculates the difference between the given DateTime and the Unix epoch (January 1, 1970).
4. div xsd:dayTimeDuration ("PT1S") → Converts the duration to seconds.
5. floor(...) → Ensures the result is an integer by rounding down.

Example Output

If the current DateTime is 2025-04-04T12:30:45Z, the XPath expression will return the Unix timestamp 1743772245.