UNIX TIMESTAMP TO COGNOS TIMESTAMP

[damaipid]

I have a date which is in unix timestamp form. Could anyone know how to convert it into a readable form in cognos.

example:
1372002752 to 23 Jun 2013 15:52:32

[blom0344]

What is the logical structure behind the UNIX timestamp format ? Apart from the first 2 digits '13' possibly refering to 2013, the logic of the timestamp escapes me

[damaipid]

Unix timestamp basically is in seconds from jan 01, 1970.  1372002752 sec is equal to 23 Jun 2013 15:52:32 in readable format. My data is in unix format which is in second, so i want it in readable format because of course user could not read the epoch format. Thanks!

[blom0344]

I do not think Cognos can fix, this, but depending on the database, you could add a user defined function that handles this:

For SQL server:

http://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server

[tjohnson3050]

I once worked with a source system (some IT help desk system) that stored dates in Unix Format.  We first tried to use a UDF function to convert the date, but doing that in a model was extremely slow.

We ended up adding two columns to our calendar table for begin and end Unix time for every given date in the calendar table.  With that in place, we joined the query subject with the source system data over to the calendar table using a complex join expression [unix date] >= [begin unix time] and [unix date] <= [end unix time].

The tricky part was accounting for things like daylight savings time.  The script I used to calculate the begin and end unix time for a calendar table (SQL Server) is available here:

http://www.cognoise.com/index.php/topic,18304.0.html

[damaipid]

Thank you very much guys for sharing your ideas.

[Alp]

Just faced the same issue. The databases store Unix time in seconds. Need a report to show a readable time stamp. Ideally in the database server time zone.
Additional requirement is that Cognos/Report should support Oracle and SQL Server.

Any good news in Cognos since 2013?

- Alex