EPOCH format to Human Readable format conversion in WEB I ?

building reports webintelligence
1

Hi Experts,

Can we convert EPOCH data and time format in to normal time and date format in web i itself by writing some sought of variable…?

I’ve read couple of articles on this, where they are mentioning to install BO on top of UNIX or some sought of technical setup required to achieve…i cant go for that procedure as my BASIS team here is too slow it’ll take forever to solve this issue.

1,270,444.943,534 this is how i am getting the values from MySQL table…i have applied a variable in web i itself i.e =[Workorder].[Createdtime]/86400+25569 (dividing it with number of seconds in a day and sum 1 jan 1970 epoch time)
After applying the above formula…i am getting result value now as 14,729,792.88 which is also alien format for me… Now when i am trying to convert this format into date and time the values are giving #FORMAT error.

Can anyone please help me on this. :hb:

umarnayab (BOB member since 2013-06-05)

2

When I worked with these the DW team translated into dates and other transforms for me.

Also they worked some magic with regards to producing a calendar table with clever formats for filtering, returning a “1” for a day ago, “2” for two days ago…, the same for weeks, months, years etc. this performed very well.

Or they could create you a DB function?

Mak 1 :uk: (BOB member since 2005-01-06)

3

Hi Mak,

Appreciate your reply! :slight_smile:

Now here with me the thing is i have to do it all.Connecting to DB pulling out data from a DS to generate a query on it and till publishing.

So in this scenario, is there any work around to achieve this requirement in the simple way.

There should be something where just a variable will work out…?

umarnayab (BOB member since 2013-06-05)

4

No easy way in Webi, that I know of.

Possible in the universe:-
https://www.google.co.uk/search?hl=en-GB&source=hp&q=epoch+dates+webi&gbv=2&oq=epoch+dates+webi&gs_l=heirloom-hp.3…1529.4243.0.4649.16.11.0.2.0.0.453.1499.0j2j3j0j1.6.0…0…1ac.1.34.heirloom-hp…11.5.1327.Db7y-igkcos

Mak 1 :uk: (BOB member since 2005-01-06)

5

Hey Mak,

i’ve done some work around from IDT side, where i have added the script and there while running the query it is giving me proper converted values for time and date.

Now applying same script in web i panel it is giving the below error

“The data type of a column in the query is not valid. (IES 10811)”

This is the script i used…

SELECT DISTINCT workorder_0.WORKORDERID, statusdefinition_0.STATUSID, statusdefinition_0.STATUSNAME, aaauser_1.FIRST_NAME, FROM_UNIXTIME(workorderstates_0.ASSIGNEDTIME/1000) AS ‘ASSIGNEDTIME’
FROM servicedesk.aaauser aaauser_0, servicedesk.aaauser aaauser_1, servicedesk.sduser sduser_0, servicedesk.statusdefinition statusdefinition_0, servicedesk.workorder workorder_0, servicedesk.workorder_threaded workorder_threaded_0, servicedesk.workorderstates workorderstates_0
WHERE workorder_0.WORKORDERID = workorder_threaded_0.WORKORDERID AND workorderstates_0.WORKORDERID = workorder_0.WORKORDERID AND statusdefinition_0.STATUSID = workorderstates_0.STATUSID AND workorder_0.REQUESTERID = sduser_0.USERID AND sduser_0.USERID = aaauser_0.USER_ID AND workorderstates_0.OWNERID = aaauser_1.USER_ID AND ((aaauser_1.FIRST_NAME<>‘Bassem’) AND (statusdefinition_0.STATUSNAME<>‘Closed’ And statusdefinition_0.STATUSNAME<>‘Cancelled’))

Any idea on this …?

umarnayab (BOB member since 2013-06-05)

6

What is the objects data type?

Mak 1 :uk: (BOB member since 2005-01-06)

7

Work Order ID = Numeric
First name = String
Status id = Numeric
status name = string
Assigned time = Numeric

umarnayab (BOB member since 2013-06-05)

8

Well, test them one by one, which causes the error?

Mak 1 :uk: (BOB member since 2005-01-06)

9

I checked the integrity from IDT side, no errors in data type.

Now from where to check for the same in web i …?

umarnayab (BOB member since 2013-06-05)

10

Add the objects 1 by 1 in Webi then run and see which returns the error.

Mak 1 :uk: (BOB member since 2005-01-06)

11

Tried Doing that, unfortunately non returned with error. weird haan… :?

Could this be a bug…?

umarnayab (BOB member since 2013-06-05)

12

If this is a derived table, try giving an explicit column using AS for all selected columns, same as below.

(workorderstates_0.ASSIGNEDTIME/1000) AS 'ASSIGNEDTIME'

Mak 1 :uk: (BOB member since 2005-01-06)

13

Yes it is a derived table from MYSQL.

And if you don’t mind…can you please explain your above suggestion more clear. :slight_smile:

umarnayab (BOB member since 2013-06-05)

14

Like this for all columns AS…

workorder_0.WORKORDERID AS 'WorkOrder'

Mak 1 :uk: (BOB member since 2005-01-06)

15

now it is giving me a

database error : [MySQL][ODBC 5.1 Driver][mysqld-4.1.18-pro-nt] you have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'statusdeination_0.statusid as ‘statusdefination’ ‘statusdefination_0.STATUSNAME’ at line 3

umarnayab (BOB member since 2013-06-05)

16

Looks like a spelling error in your syntax, is that ID a number or a character?

Mak 1 :uk: (BOB member since 2005-01-06)

17

Checked with the spellings…all good with that. and ID is a dimension number with data type numeric.

umarnayab (BOB member since 2013-06-05)

18

:?: What versions and service pack of BO.
:?: What My SQL Database version and service pack.
:?: Connectivity type and version used.

:?: Does this code run in My SQL itself?

Mak 1 :uk: (BOB member since 2005-01-06)

19

BI VERSION : 14.0.6
MYSQL DB : 2005 no idea about service pack. :frowning:

This code is not running on MYSQL…
i am pulling a table in IDT and publishing a universe,using it in web i.

I applied the script on IDT level, it worked fine there when running a query there itself…same thing i am trying to do in web i…leading me to errors…

umarnayab (BOB member since 2013-06-05)

20

Well, if it won’t run there it wont work in BO, do you get another error?

So it looks like you are running 4.0 with patches, although you havent given more the full number.

Also, you need to check whther such an old version of My SQL is supported as well as the method you are using to connect ODBC…etc

Mak 1 :uk: (BOB member since 2005-01-06)