Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
Ektoras Giorgos
New Member.
3794 views

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?

0 Likes
4 Replies
DMiller1 Absent Member.
Absent Member.

RE: How to convert a UNIX Timestamp to date?

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.

0 Likes
Ektoras Giorgos
New Member.

RE: How to convert a UNIX Timestamp to date?

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

0 Likes
Micro Focus Expert
Micro Focus Expert

RE: How to convert a UNIX Timestamp to date?

Try and select it into a character field.  

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

0 Likes
Chuck Edgin Absent Member.
Absent Member.

RE: How to convert a UNIX Timestamp to date?

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.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.