UCMDB and UD Practitioners Forum (Previously CMS)
Showing results for 
Search instead for 
Do you mean 

UCMDB DB time calculation algorithm

Advisor

UCMDB DB time calculation algorithm

[ Edited ]

Hi,

I wonder how ucmdb calculates the 22 digit host_last_boot_time values to usual date format as we know.

For example;
1271575551000 value from db / view "a_host_last_boot_time" is "Host Boot Time Sun Apr 18 2010 10:25 AM EEST" in ucmdb ui.

Any ideas ?

Thanks...

 

 

P.S. This thread has been moevd from Application Perf Mgmt (BAC / BSM) Support and News Forum to CMS and Discovery Support and News Forum. - Hp Forum Moderator

9 REPLIES
Honored Contributor

Re: UCMDB DB time calculation algorithm

I do believe that it is stored in the "timestamp" value.
Honored Contributor

Re: UCMDB DB time calculation algorithm

Hi,

this value is the epoch (or Posix) time , i.e: number of milliseconds from 00:00,January 1, 1970, UTC.
Advisor

Re: UCMDB DB time calculation algorithm

Thanks for your answers but I need calculation formula.

As you know in db all the data about host_last_boot_time is timestamp. I need an SQL to convert them to date format like dd/mm/yyyy hh:mm:ss.

Honored Contributor

Re: UCMDB DB time calculation algorithm

No offense, but did you try to google on the " timestamp to datetime"?
To get something like this: http://psoug.org/reference/timestamp.html
Honored Contributor

Re: UCMDB DB time calculation algorithm

Hi,

Are you trying to query the CMDB tables directly ? you can use UCMDB APIs (web/Java) to get CIs properties in their format (data/String/etc...) without access the DB tables.
Advisor

Re: UCMDB DB time calculation algorithm

Our DBA team is querying the ucmdb db directly, they want to get host boot times from db at once. I am not sure if they can get it from API.

They beleive that there must be a kind of function that calculates and stores the boot time value to db, if anybody knows the formula we can directly extract it.
Honored Contributor

Re: UCMDB DB time calculation algorithm

Hi,

They can get it with the APIs

and for the formula its a simple Date stored in DB, your DBA should know it
Advisor

Re: UCMDB DB time calculation algorithm

Here is the function/formula thet one of our dba created to convert the ucmdb db host boot time value to date time format.

thanks for your ideas.

CREATE OR REPLACE FUNCTION SYS."GETLASTUPDATE_REVERSE_SERDAR" ( lastupdated in number) RETURN date
IS
cur_date date ;
BEGIN
select lastupdated/86400000 + TO_DATE( (case when TZ_OFFSET('Europe/Istanbul') like '%03:00%' then '197001010300'
else '197001010200' end ),'YYYYMMDDHHMI'))
into cur_date from dual;
RETURN cur_date ;
END;
/

select getlastupdate_reverse(1290925242747) from dual;
Advisor

Re: UCMDB DB time calculation algorithm

CREATE OR REPLACE FUNCTION SYS."GETLASTUPDATE_REVERSE_SERDAR" ( lastupdated in number) RETURN date
IS
cur_date date ;
BEGIN
select lastupdated/86400000 + TO_DATE( (case when TZ_OFFSET('Europe/Istanbul') like '%03:00%' then '197001010300'
else '197001010200' end ),'YYYYMMDDHHMI'))
into cur_date from dual;
RETURN cur_date ;
END;
/

select getlastupdate_reverse(1290925242747) from dual;
//Add this to "OnDomLoad" event