Db2 for IBM i: Watch Your Epoch

I have to admit that I didn’t know what an epoch was until a recent customer engagement. This customer enlisted the assistance of our IBM Technology Expert Labs team to help build a solution that would continually stream IBM i system performance data to a Splunk server. The message sent to the Splunk server needed to include the epoch value associated with the time that system performance data was generated.

With this epoch requirement, our Expert Labs team was pleasantly surprised to find that Db2 for i SQL support provides an EXTRACT function which has the ability to return the epoch value associated with a timestamp value. This built-in SQL function made it simple to return the epoch value of 1,757,182,530 seconds for the input timestamp of 2025-09-06 18:15:30 as demonstrated in the Run SQL Scripts screen captured below.

A solution to generate a message to Splunk with the required epoch value was put together quickly using this EXTRACT function. As testing began, however, we discovered that the EXTRACT function generated an unexpected epoch value. The prior example was run on an IBM i server with the time zone set to US Central Time. So, all of us working on the project assumed that the epoch value returned by the EXTRACT function would be the number of seconds that have elapsed since January 1, 1970 00:00:00 (US Central).

Closer examination of the epoch value returned by the SQL function showed that the time zone setting of your IBM i server does not matter. The EXTRACT function always returns an epoch value which is the number of seconds that have elapsed since January 1, 1970 00:00:00 (UTC). Depending on your application requirements, this may be good or bad news. For our client, the epoch value needed to be based on the time zone of the server and not UTC.

The IBM Expert Labs team relied on another SQL capability - the current timezone special register – to address this requirement. As the following example shows, subtracting the current timezone value from the input timestamp allows the epoch value to be generated based on system’s time zone instead of UTC.