i have i timestamp (1363161273) and i want to translate it to date. Does anyone know how?
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.