How to convert milliseconds API fields to Apache Solr Date format using Microsoft Excel

Request for extracts raw data for customer needs cannot be avoided as in some cases there is data that necessary to be extracted. For Example, raw data on form management. To perform this, customers should take advantage of access from the 3dolphins API or request the support team to collect the data.

However, sometimes there is a case on 3Dolphins API response result is created_date field in milliseconds as shown below.

Postman Output

The image above shows the Query API generated using Postman. It shows the output generated in the fields for tanggal_lahir and created_date is still on milliseconds, while the output required by the customer is in date-time format.

If it happens, data retrieval must on the Apache Solr database by query directly without going through the 3Dolphins API so, the format will match the date field in Apache Solr.

Apart from this, you may still use the 3Dolphins API by converting the field into a .xls or CSV file, and then the format will be changed using a formulation in Microsoft Excel.

To convert millisecond fields to date format in Microsoft Excel by following these steps:

1. Add the following formulas to convert fields in Microsoft Excel. The formulation is:$=({the intended field column} /86400000)+DATE(1970;1;1)$

2. The picture below shows created_date on the H field still uses milliseconds. Meanwhile, the I field are results after field H converted into date format using the formulation like this:$(H2/86400000)+DATE(1970;1;1)$

3. Format result will be similar as date-time format as the date format in Apache Solr in Microsoft Excel, i.e. yyyy-mm-ddThh:mm:ss.ssZ

Excel File