Question & Answer
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)
[{"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":"LOB45","label":"Automation"}}]
Was this topic helpful?
Document Information
Modified date:
13 June 2019
UID
swg21376100