BusinessObjects Board

Convert number of seconds to time format in BOXIR2 Informix

building reports webintelligence
#1

Good morning

I hope you can help.

I am trying to convert the number seconds to a time format HH:MM:SS in BOXIR2 from an Informix Database. This is a UNIX timestamp. It is easy enough to export to Excel and do the format/conversion… but is tehre actually something I can do in BOXIR2?

was able to do with Oracle using to_date and then dividing the number by 86400 and adding with start date. but don’t know the informix equivalent. if someone has done similar thing can you please let me know.

Thank you very much

Stephen

smcclintock (BOB member since 2011-05-11)

#2

Welcome to B:bob:B! Do you want a universe level solution or Web-I document level solution?

This link may give you an idea on how to do this in universe.

Marek explains how you can do the same in the report level here.

Jansi :india: (BOB member since 2008-05-12)

#3

Good morning

Thank you for your advice.

I have tried the report level suggestion, where the LOGIN].[Log In Time] is my Infomix Unix timestamp in seconds. Please see below.

=FormatNumber(Floor([LOGIN].[Log In Time]/3600);“00”) + “:” + FormatNumber(Floor(Mod([LOGIN].[Log In Time];3600)/60) ;“00”) + “:” + FormatNumber(Mod(Mod([LOGIN].[Log In Time];3600) ;60);“00”)

This has brought back a result, but I am now unable to format to hh:mm:ss… Any further suggestion?

Thanks
in advance

Stephen

smcclintock (BOB member since 2011-05-11)

#4

Did you get this resolved? If not…

What is the data type of the source value? Do you have an example - before value and converted value exactly?

Patrick McDonough (BOB member since 2002-08-20)