I am having some trouble correctly formatting a string into a viable datetime that I can use in a table (must be in a time/datetime/non-string format to sort correctly). The field is called avg_runtime. The API call returns something like this:

{
"data": [
    {
        "id": "01",
        "avg_runtime": "0:05:12.026100",
        "avg_runtime_seconds": 312.0261,
        "file_name": "myFile1",

    },
    {
        "id": "02",
        "avg_runtime": "0:10:03.936321",
        "avg_runtime_seconds": 603.936321,
        "file_name": "myFile2"
    }
  ]
}

I added a second field to the backend that returns avg_runtime_seconds as a decimal in seconds. One of the challenges is that, in some instances, the return value is days, not just hh:mm:ss, but includes days, like this: 9 days, 17:30:02.813297. This is a problem in sorting as 9 days, 17:30:02.813297sorts out of order with11 days, 2:43:52.300138` (I am using Material UI DataGrid functionality for sorting). So I need to convert these strings into a datetime or some non-string value that be properly sorted. Here's how I currently format values in my API call:

  async function getData() {
    await axios
        .get(`https://myapi.com/${ runmode }/products`, {
        headers: {
            'Content-Type': 'application/json'
        }
        })
        .then((response) =>{
            var this_data = response.data.data;
            setData(
                this_data.map((x) => {
                    return {
                        id: parseInt(`${x.product_id}`),
                        file_name: `${x.file_name}`,
                        avg_runtime: `${x.avg_runtime}`,
                        avg_runtime_seconds: `${x.last_rundate}`   
                    }
                })
            );
            setValidationList(
                tthis_data.map((x) => x.product_id)
            )
            setLoadingData(false);
        });
}

I've tried using avg_runtime: new Date(${x.avg_runtime}).toDateString(), but that returns Invalid Date. How can I format this value into a usable (and sortable) time/datetime format?

UPDATE: I was able to utilize Value Getter as suggested below, but it still would not sort correctly, even using numeric values. I found what seems to be the source of the error. While the decimal for the avg_runtime_seconds is set to 6 places, the sorting doesn't seem to take the overall value into consideration. Look at the Avg Runtime Seconds difference between 9 days, 17 hrs, 45 mins, compared to 2hrs, 21 mins. The sorting seems to be based on the character placement, rather than taking into consideration where the decimal is located. Anything that would help fix this?

CodePudding user response：

This was a difficult issue to resolve. The first thing I had to do was get a field with a numeric value that I could reference when sorting. I had the avg_runtime in seconds as a float, but the @mui DataGrid only seemed to work properly with whole numbers so I converted them using Math.ceil (round up). I also noticed that the original suggestion to use value getter seemed to still sort by the value that is displayed in the DataGrid. I tried another suggestion using GridRenderCellParams. This worked and I created a sandbox to test it. The below code is what I used, but here is the sandbox to a better visualization of the solution. *Note-the avg_runtime_sconds values have already been changed to whole integers below instead of using Math.ceil.

import Box from "@mui/material/Box";
import { DataGrid, GridColDef, GridRenderCellParams } from "@mui/x-data-grid";

const columns: GridColDef[] = [
  { field: "id", headerName: "ID", width: 90 },
  {
    field: "file_name",
    headerName: "File Name",
    width: 150,
    editable: true
  },
  {
    field: "avg_runtime",
    headerName: "Avg Runtime Orig",
    width: 150,
    editable: true
  },
  {
    field: "avg_runtime_seconds",
    headerName: "Avg Runtime Ref Secs",
    width: 150,
    editable: true,
    renderCell: (params: GridRenderCellParams) => (
      <div>{params.row.avg_runtime}</div>
    )
  }
];

const rows = [
  {
    id: "01",
    avg_runtime: "0:05:12.026100",
    avg_runtime_seconds: 312,
    file_name: "myFile1"
  },
  {
    id: "02",
    avg_runtime: "0:10:03.936321",
    avg_runtime_seconds: 603,
    file_name: "myFile2"
  },
  {
    id: "03",
    avg_runtime: "9 days, 17:45:04.823807",
    avg_runtime_seconds: 841670,
    file_name: "myFile3"
  },
  {
    id: "04",
    avg_runtime: "11 days, 3:01:15.710670",
    avg_runtime_seconds: 961275,
    file_name: "myFile4"
  }
];

export default function DataGridDemo() {
  return (
    <Box sx={{ height: 400, width: "100%" }}>
      <DataGrid
        rows={rows}
        columns={columns}
        pageSize={5}
        rowsPerPageOptions={[5]}
        checkboxSelection
        disableSelectionOnClick
        experimentalFeatures={{ newEditingApi: true }}
      />
    </Box>
  );
}