cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
1003
Views
10
Helpful
2
Replies
Highlighted
Collaborator

What is the UCS Manager csv export time field format

Hi,

I exported an Events log from UCS Manager: Equipment>Rack-Mounts>Servers>Server 4>[Events] tab > Exporttimeformat.png

 

 

 

Note the way the time is diplayed in the GUI - the date/time clearly shows 02:07:37 UTC time on 2018-03-22.

Here is the same raw record in the csv export file - along with the header:

 

Affected object,Code,ID,Cause,Created at,User,Description
sys/rack-unit-4,E4196863,274515,transition,1521684457000,internal,[FSM:STAGE:SKIP]: deassert reset-bypass(FSM-STAGE:sam:dme:ComputePhysicalAssociate:MarkAdapterForReboot)

I've shown the Created at value in underlined red

 

How on earth does 1521684457000 convert to  02:07:37 UTC time on 2018-03-22?

 

More importantly, how do I get Excel to convert 1521684457000 to  02:07:37 UTC time on 2018-03-22?

 

When I enter then time 2018-03-22 02:07:37 in excel it becomes 43181.0886226852

 

Does anyone have the formula that UCS Manager uses to be able to convert the UCSM format to real times?

 

 

RedNectar
aka Chris Welsh


Don't forget to mark answers as correct if it solves your problem. This helps others find the correct answer if they search for the same problem

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Collaborator

[Edit: Not sure if it is cool to accept your own solution, but it stops the emails from the support community asking if I got the help I needed. Yes - I got help from Walter but needed to elobarate to provide a complete answer for anyone else who ends up here]

 

Thanks to Walter who pointed me in the right direction, but didn't quite make it (out by a factor of 1000).

 

SO to convert UCS Manager timestamps into current time (UTC), take the Created at time, divide it by 86,400,000 (=60*60*24*1000) then add to that "1/1/1970".

 

IN excel, if your Created at time is in cell E2, enter the following into an empty cell

=E2/(86400000)+"1/1/1970"

 The result will be an excel timestamp. Format it to a date or time field to get the result you want.

 

RedNectar
aka Chris Welsh


Don't forget to mark answers as correct if it solves your problem. This helps others find the correct answer if they search for the same problem

View solution in original post

2 REPLIES 2
Highlighted
Advocate

Hi Chris

Would this be of any help ?

https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html

or

https://www.berezniker.com/content/pages/office/excel-convert-unix-time-excel-time

 

Be aware that Unix time is the number of seconds since January 1, 1970 for UTC/GMT while Excel uses the number of days and fractions of the day for the local time zone.

If you convert a value such as 1322164881 using =CELL/(60*60*24)+"1/1/1970" you will get 2011-11-24 20:01:21 which is correct for UTC or the GMT time zone.

Highlighted
Collaborator

[Edit: Not sure if it is cool to accept your own solution, but it stops the emails from the support community asking if I got the help I needed. Yes - I got help from Walter but needed to elobarate to provide a complete answer for anyone else who ends up here]

 

Thanks to Walter who pointed me in the right direction, but didn't quite make it (out by a factor of 1000).

 

SO to convert UCS Manager timestamps into current time (UTC), take the Created at time, divide it by 86,400,000 (=60*60*24*1000) then add to that "1/1/1970".

 

IN excel, if your Created at time is in cell E2, enter the following into an empty cell

=E2/(86400000)+"1/1/1970"

 The result will be an excel timestamp. Format it to a date or time field to get the result you want.

 

RedNectar
aka Chris Welsh


Don't forget to mark answers as correct if it solves your problem. This helps others find the correct answer if they search for the same problem

View solution in original post

Content for Community-Ad