Walker News

DB2 SQL Function That Converts UNIX Epoch Time To Calendar Date

The IBM DB2 doesn’t provide a native SQL function to convert UNIX epoch time to human-readable calendar date/time format.

However, administrator and developer can create a SQL scalar function (UDF, shorts for user-defined function) to get this job done.
As compare to parsing SQL output with the GNU date command, this UDF is much intuitive and convenient to use in SQL SELECT statement.

Create a UDF to convert Unix time value in SQL

NOTE:

1. These steps are executing in DB2 CLI environment (DB2 9.7; Linux platform).

2. This UDF, let’s call it e2d, returns the date in UTC+0 timezone (no local time translation).

3. The e2d UDF uses LPAD function which is not available prior to DB2 9.7.

Firstly, connect to database:
db2 connect to your_db_name

Now, create the UDF function (to avoid typo, just copy and paste):
db2 "
 CREATE FUNCTION e2d (ept INT)
 RETURNS CHAR(23)
 RETURN
 (

  CHAR
  (
    DATE
    (
      INT(ept/86400)
      + 
      DAYS('1970-01-01')
    )
  ,ISO) ||

  ' ' ||

  LPAD
  (
    mod
    (
      int(ept/3600)
    ,24)     
  ,2,'0') || 

  ':' ||

  LPAD
  (
    mod
    (
      int(ept/60)
    ,60)
  ,2,'0') ||
 
  ':' ||

  LPAD
  (
    mod
    (
      int(ept)
    ,60)
  ,2,'0')

 ) || 

 ' UTC'
"

As soon as the create function statement completes without error (unless you’ve made a typo), the UDF (i.e. e2d) is recognized and usable. To test it, for example:
db2 "SELECT e2d(1234567890) FROM syscat.functions"
db2 "SELECT e2d(86400) FROM syscat.functions"

To delete or remove the user-defined function:
db2 drop function e2d

To view or examine the UDF created in a database:
db2 "SELECT * FROM SysCat.Functions"

Custom Search

2017  •  Privacy Policy