IBM Support

How to convert a UNIX time stamp to a usable date by using SQL

Question & Answer


What is a sample SQL statement that uses a UNIX time stamp as a meaningful date?


IBM® Content Manager OnDemand stores dates as UNIX time stamps. You can use SQL to convert the time stamp.


A time stamp is based on the number of seconds, not the number of days.  Therefore, you must multiply the number of days by 86400 to convert them to seconds. The following SQL statements can be used to get a count of the number of logons for users between two dates:

   SELECT count(userid), userid
      FROM qusrond/sl2
      WHERE msg_num =32 AND
      Time_stamp BETWEEN (days('1/26/2007')-719163)*86400 AND
      GROUP BY userid
  ORDER by userid

Note: The time stamp in the Content Manager OnDemand system log is a time stamp with a time zone data type, so the time is stored in Coordinated Universal Time (UTC).  If you want to get the counts for the dates at the server location, you must make an adjustment based on the difference in time between the server and UTC.  The following sample shows how to get the counts, where the -5 is the offset for your time zone from UTC:


[{"Product":{"code":"SSB2EG","label":"Content Manager OnDemand for i"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Common Server","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"6.1;5.4;5.3;5.2;5.1","Edition":"","Line of Business":{"code":"LOB15","label":"Integration"}}]

Document Information

Modified date:
13 June 2019