How to apply date/time sorting on a column which has date/time as string in G1 table in gramex

Question

I have a G1 table (Gramex) in UI which has columns with date and time but as string. When the user sorts the respective column with the help of inbuilt sort option, it sorts the data as string not as date.

Answer 1

Approach 1

The easiest way would be if the date and time were formatted like YYYY-MM-DD HH:MM:SS. For example, '2022-09-04 03:02:01'. This is a string that can be sorted consistently with the datetime.

You can create such strings in Python with the datetime.isoformat() function.

Approach 2

If your dates are stored in a different format, e.g. 04-09-2022, etc., then IF the data is loaded from a file, not a database, then you can modify the data with the function: transform to convert the column to the right data format.

For example:

url:
  continent:
    pattern: /data
    handler: FormHandler
    kwargs:
      url: data.csv
      function: data.assign(date_col=pd.to_datetime(data[date_col]).strftime('%Y-%m-%dT%H:%M:%S'))

This converts the date_col (replace this with your date column name) from any date format Pandas recognizes into a YYYY-MM-DD HH:MM:SS type format that is sortable.

If the data is loaded from a database, then you DO need to convert it into a sortable format first.