Unix Timestamp

[Kevin S]

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

[geoffwilde]

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;                                                           

[tbrown]

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

[RHPharr]

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