OnDemand User Group
Support Forums => iSeries => Topic started by: Kevin S on October 25, 2006, 08:18:20 AM
-
Does anyone have any idea how to convert a unix timestamp using SQL. I do know that the unix timestamp is a second count from 1-1-1970. I can use arsdate to convert the timestamp in QSHELL, but not sure in SQL. Thanks in advance. Kevin
-
our DBA is using castdate function
SELECT SUBSTR(APPLID,1,8)
,CAST('1969-12-31' AS DATE) + PDATE DAYS
,RIGHT((' ' || STRIP(DIGITS(COUNT(*)),LEADING,'0')),9)
,PDATE
,DNAME
,DNAME_WHOLE
,B.NAME
FROM SROLSHE.DIRECTORY , ARSSERVR.ARSNODE B
WHERE (SUBSTR(APPLID,1,8) = 'LEBLL01P'
OR SUBSTR(APPLID,1,8) = 'LECHK01P')
AND (CAST('1969-12-31' AS DATE) + PDATE DAYS) > '1997-12-31'
AND (CAST('1969-12-31' AS DATE) + PDATE DAYS) < '2006-02-12'
AND NID = PRI_NID
GROUP BY
APPLID
,PDATE
,DNAME
,DNAME_WHOLE
,B.NAME
WITH UR;
-
I beleive the example is correct for a date field but not for a timestamp. A timestamp is based on the number of seconds and not just the number of days. You would need to multiply the days by 86,400 to convert that to seconds. I have used the following SQL 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
Caution: The timestamp in the system log is a timestamp with timezone data type so the time is actually stored in UTC. If you want to get the counts for the dates at the server location, you will need to make an adjustment based on the difference in time between the server and UTC. Here is one way to do that:
date(((time_stamp-5*3600)/86400)+719163)
Where the -5 is the offset for your time zone from UTC
-
Fun with arsdate date and date/time
Select
--date
STOP_DATE,
DATE('1969-12-31') + stop_date DAYS,
CHAR(DATE('1969-12-31') + stop_date DAYS, USA),
--date/time
last_update,
TIMESTAMP('1970-01-01-00.00.00') + last_update SECONDS,
CHAR(DATE(TIMESTAMP('1970-01-01-00.00.00')+ last_update SECONDS),USA)
from ROOT.ARSSEG