IBM Support

RAW date format in CSV exported reports

Question & Answer


Question

How to convert date values in CSV (comma separated values) exported reports from RAW format to a human readable format ?

Cause

When exporting Tivoli Storage Manager - Data Protection for VMware GUI reports with version 7.1.1 or higher in CSV format, the date values are in RAW format.
This means that the date values are expressed in milliseconds and in Unix time (a.k.a. POSIX time or Epoch time) which is the time elapsed since 00:00:00 Coordinated Universal Time (UTC) on 1 January 1970.

Answer

To translate the dates using spreadsheet tools, the following needs to be done :

To get the UTC time insert in the spreadsheet cell :

=((Cell_id/1000)/86400+25569)

Then edit the cell properties to tell it is a date format and you will get the UTC time in human readable format.

To adjust it for another timezone, add or subtract the number of hours (in seconds) relative to UTC :

=(((Cell_id/1000)+/-timezone difference)/86400+25569)

For example to subtract 2 hours, the formula is :

=(((C4/1000)-(2*3600))/86400+25569)

[{"Product":{"code":"SS8TDQ","label":"Tivoli Storage Manager for Virtual Environments"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Component":"Data Protection for VMware","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"7.1.1;7.1.2;7.1.3;7.1.4","Edition":""}]

Document Information

Modified date:
17 June 2018

UID

swg21691127