cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
1621
Views
20
Helpful
3
Replies

What is the UCS Manager csv export time field format

RedNectar
Advocate
Advocate

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. Forum Tips: 1. Paste images inline then edit>Image Size Large- don't attach. 2. Always mark helpful and correct answers, it helps others find what they need.
2 Accepted Solutions

Accepted Solutions

Walter Dey
Advocate
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.

View solution in original post

RedNectar
Advocate
Advocate

[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. Forum Tips: 1. Paste images inline then edit>Image Size Large- don't attach. 2. Always mark helpful and correct answers, it helps others find what they need.

View solution in original post

3 Replies 3

Walter Dey
Advocate
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.

RedNectar
Advocate
Advocate

[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. Forum Tips: 1. Paste images inline then edit>Image Size Large- don't attach. 2. Always mark helpful and correct answers, it helps others find what they need.