IBM Support

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

Technote (FAQ)


Question

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

Cause

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

Answer

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
      (days('1/27/2007')-719162)*86400-1
      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:

   date(((time_stamp-5*3600)/86400)+719163)

Document information

More support for: Content Manager OnDemand for i
Common Server

Software version: 5.1, 5.2, 5.3, 5.4, 6.1

Operating system(s): IBM i

Reference #: 1376100

Modified date: 10 June 2014


Translate this page: