How to convert a UNIX Timestamp to date?

i have i timestamp (1363161273) and i want to translate it to date. Does anyone know how?

  • Where is this from?  it is from an "Accept ws-timestamp from " statement or from c$listdir, etc.?  I am not used to seeing it as 10 digits.

  • Where is this from?  it is from an "Accept ws-timestamp from " statement or from c$listdir, etc.?  I am not used to seeing it as 10 digits.

  • it is from a datebase (MySQL). There is a table that contain timestamp

  • Try and select it into a character field.  

    If that does not give the correct result, use the to_char() function on it.

  • If this is a MySQL column stored with a DATE, DATETIME, or TIMESTAMP data type, you can use MySQL's  DATE_FORMAT() function as part of your query. I think it's something like this:

    SELECT DATE_FORMAT(my_date_field, '%Y-%m-%d') FROM my_table WHERE ...
    
    2013-05-28
    

    There are several other date and time functions described in the MySQL docs.

    However, your original question was how to convert a UNIX time. Maybe you don't have any control over the query being run so you're forced to deal with this format.  You can do this in COBOL (specifically ACUCOBOL) using the DATE-OF-INTEGER() intrinsic function to convert this to a standard COBOL date.

    The UNIX Time (also known as POSIX time) is an integer containing the number of seconds since the UNIX Epoch, 01 Jan 1970.

    First, you'll need to divide the UNIX time by 86400 to get the days (there are usually 86400 seconds in a day).  Then you need to add 134775 to that to get the Gregorian days (number of days succeeding 31 December 1600 in the Gregorian calendar).

    01  UNIX-TIME       PIC 9(10) VALUE 1363161273.
    01  COMPUTED-DAYS   PIC 9(6).
    
    01  OUTPUT-DATE     PIC 9(8).
    01  OUTPUT-DATE-GROUP REDEFINES OUTPUT-DATE.
        03  YEAR        PIC 9(4).
        03  MONTH       PIC 9(2).
        03  MDAY        PIC 9(2).
    01  OUTPUT-DATE-FORMATTED.
        03  YEAR        PIC 9(4).
        03  FILLER      PIC X VALUE '-'.
        03  MONTH       PIC 9(2).
        03  FILLER      PIC X VALUE '-'.
        03  MDAY        PIC 9(2).
    
    DIVIDE UNIX-TIME BY 86400 GIVING COMPUTED-DAYS
    ADD 134775 TO COMPUTED-DAYS
    MOVE FUNCTION DATE-OF-INTEGER(COMPUTED-DAYS) TO OUTPUT-DATE
    MOVE CORRESPONDING OUTPUT-DATE-GROUP TO OUTPUT-DATE-FORMATTED
    
    Result:  2013-03-13
    

    DISCLAIMER:  This should give a reasonably accurate result, but may not be completely accurate due to the vagaries of leap-seconds, etc. Using the date and time functions within MySQL should give a more comprehensive and accurate result.