An Oracle blog about BI Publisher

  • RTF
    September 20, 2007

Time for Unix?

More on dates and we're getting a little weird here. Now, Im not sure how widespread the use of UnixTime is in reports but a colleague, Vinod, asked how Publisher could handle unixtime and format it into a readable date.

For those of you that dont know about it, its the number of seconds (not including leap seconds) since midnight UTC January 1st, 1970. I have to admit I have not seen unixtime outside the context of an OS and definitely not used in a report for a date - but hey, it's a requirement.

However, Vinod managed to work it all out based on a blog entry from a while ago on data calculations - 'A Date Addition' heres how he did it.

1183100412 in unix time is actually 29 Jun 2007 in real money ... to get this into your output you would use.

<?xdoxslt:ora_format_date_offset('1970-01-01', MOD_T div 86400, ?+?)?> where MOD_T is the element holding the UNIX time value.

The 86400 gets your seconds to days et voila and that ladies and gentlefolk is unixtime ... done!

Join the discussion

Comments ( 3 )
  • Ron Morton Wednesday, August 27, 2008
    I just googled ora_format_date_offset and it ONLY came up with entries from this blog and from the BIP forum. Where is this documented and how many other such "secret" functions are there?
  • Don Eubanks Thursday, September 18, 2008
    When using a JDE database and returning a date from a table such as the F4211 transaction date (TRDJ) the date returned is a numeric value such as "105052" which is a JDE form of julian. How can this be formatted to appear as a date.
  • nikhil Monday, June 1, 2009
    What if i need to extract time from unix date?
    The code worked for me was,

    but now i need to extract time as well, how to do this?
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.