Using uCMDB dates directly from the database

[Admin]

Many of us like to create reports from other tools which usually means connecting to the backend database, either SQL Server or Oracle.  For example you may want to create a report that tells you how many CIs of which type have been created since a particular date and it is unlikely that uCMDB can do this in a big instance as it returns a Failed to load report  error.  The difficulty is that when you look at the date fields you will just see an impossibly long number.

So the following is an explanation of what this is and how you can convet it to a date you can report on or query against:

The date is what is know as a epoch or posix time.  Loosley this is the number of milliseconds since 00:00 Jan 01 1970.  GMT/ZULU/UTC

Most Microsoft products store date times as a number with the left hand side of the decimal being the date and the right hand side being the time.

So to translate it into a date and time you need to:
Divide by 1000 (From millseconds to seconds)
Divide by 60 (From seconds to minutes)
Divide by 60 (From minutes to hours)
Divide by 24 (From hours do days)
You now have a number that tells you how many days (as a decimal) since 00:00 Jan 01 1970

But you will also need to make a variance for your time zone.  For example my time zone is +10 hours so I add (you may need to take away):
10*60*60*1000
36000000

Also many Microsoft products such as Excel and Access base their dates on 00:00 Dec 30 1899 so if you are using one of these tools you may need to add 2209161600000 to the number.  This is the difference between 1/1/1970 and 30/12/1899.

So for a specific example in MS Access spelled out for clarity:
CreateTime: Format((([A_CREATE_TIME]+2209161600000+36000000)/1000/60/60/24),"dd-mmm-yyyy hh:nn:ss")
[A_CREATE_TIME] = Field Name
+2209161600000 = Difference between 1/1/1970 and 30/12/1899
+36000000 = +10 hours GMT
/1000 = Milliseconds
/60 = seconds
/60 = minutes
/24 = hours