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
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:
13 June 2019