search

mathesar-foundation / mathesar

Web application providing an intuitive user experience to databases.
https://mathesar.org/
GNU General Public License v3.0
2.43k stars 336 forks source link

Time Zones are inconsistent in Data Explorer #3184

Open mathemancer opened 1 year ago

mathemancer commented 1 year ago

Description

The TIMESTAMP WITH TIME ZONE type is behaving inconsistently in Data Explorer. Without summarizing:

image

Records json from response:

  "records": {
    "count": 2,
    "grouping": null,
    "preview_data": null,
    "results": [
      {
        "Patrons_First Name": "Alice",
        "Patrons_Last Name": "Thompson",
        "Patrons_Email": "athompson76@stein.com",
        "Books_Title": "A Borrowed Man",
        "Checkouts_Checkout Time": "2023-08-28T04:00:00.0Z AD",
        "Checkouts_Due Date": "2023-09-11 AD",
        "Checkouts_Check In Time": null
      },
      {
        "Patrons_First Name": "Alice",
        "Patrons_Last Name": "Thompson",
        "Patrons_Email": "athompson76@stein.com",
        "Books_Title": "Black Powder War",
        "Checkouts_Checkout Time": "2023-08-28T06:18:03.062962Z AD",
        "Checkouts_Due Date": "2023-09-11 AD",
        "Checkouts_Check In Time": null
      }
    ]

With summarizing:

image

Records json from response:

  "records": {
    "count": 1,
    "grouping": null,
    "preview_data": null,
    "results": [
      {
        "Patrons_Email_grouped": "athompson76@stein.com",
        "Patrons_First Name_group": "Alice",
        "Patrons_Last Name_group": "Thompson",
        "Books_Title_agged": [
          "A Borrowed Man",
          "Black Powder War"
        ],
        "Checkouts_Checkout Time_agged": [
          "2023-08-28T04:00:00Z",
          "2023-08-28T06:18:03.062962Z"
        ],
        "Checkouts_Due Date_agged": [
          "2023-09-11"
        ],
        "Checkouts_Check In Time_agged": [
          null
        ]
      }
    ]
  },

It seems like the response in the summarized case doesn't include the decimal place (value 0) for the first checkout time, but it does include it in the non-summarized case. Then, the front end is handling that differently, and seems to break when there's no decimal place.

Expected behavior

To Reproduce

See the screenshots. Generally, one can see this by summarizing a timestamp with time zone column with at least one timestamp that doesn't include decimal places (i.e., the timestamp occurs precisely on a second).

Additional context

The response without summarizing:

{
  "query": {
    "schema": 2,
    "initial_columns": [
      {
        "alias": "Patrons_First Name",
        "id": 110
      },
      {
        "alias": "Patrons_Last Name",
        "id": 111
      },
      {
        "alias": "Patrons_Email",
        "id": 112
      },
      {
        "alias": "Books_Title",
        "id": 2,
        "jp_path": [
          [
            109,
            290
          ],
          [
            266,
            1
          ],
          [
            79,
            86
          ]
        ]
      },
      {
        "alias": "Checkouts_Checkout Time",
        "id": 363,
        "jp_path": [
          [
            109,
            290
          ]
        ]
      },
      {
        "alias": "Checkouts_Due Date",
        "id": 389,
        "jp_path": [
          [
            109,
            290
          ]
        ]
      },
      {
        "alias": "Checkouts_Check In Time",
        "id": 416,
        "jp_path": [
          [
            109,
            290
          ]
        ]
      }
    ],
    "transformations": [
      {
        "type": "filter",
        "spec": {
          "lesser": [
            {
              "column_name": [
                "Checkouts_Due Date"
              ]
            },
            {
              "literal": [
                "2023-09-18"
              ]
            }
          ]
        }
      },
      {
        "type": "filter",
        "spec": {
          "null": [
            {
              "column_name": [
                "Checkouts_Check In Time"
              ]
            }
          ]
        }
      }
    ],
    "base_table": 21,
    "display_names": {
      "Patrons_First Name": "Patrons_First Name",
      "Patrons_Last Name": "Patrons_Last Name",
      "Patrons_Email": "Patrons_Email",
      "Books_Title": "Books_Title",
      "Checkouts_Checkout Time": "Checkouts_Checkout Time",
      "Checkouts_Due Date": "Checkouts_Due Date",
      "Checkouts_Check In Time": "Checkouts_Check In Time"
    }
  },
  "records": {
    "count": 2,
    "grouping": null,
    "preview_data": null,
    "results": [
      {
        "Patrons_First Name": "Alice",
        "Patrons_Last Name": "Thompson",
        "Patrons_Email": "athompson76@stein.com",
        "Books_Title": "A Borrowed Man",
        "Checkouts_Checkout Time": "2023-08-28T04:00:00.0Z AD",
        "Checkouts_Due Date": "2023-09-11 AD",
        "Checkouts_Check In Time": null
      },
      {
        "Patrons_First Name": "Alice",
        "Patrons_Last Name": "Thompson",
        "Patrons_Email": "athompson76@stein.com",
        "Books_Title": "Black Powder War",
        "Checkouts_Checkout Time": "2023-08-28T06:18:03.062962Z AD",
        "Checkouts_Due Date": "2023-09-11 AD",
        "Checkouts_Check In Time": null
      }
    ]
  },
  "output_columns": [
    "Patrons_First Name",
    "Patrons_Last Name",
    "Patrons_Email",
    "Books_Title",
    "Checkouts_Checkout Time",
    "Checkouts_Due Date",
    "Checkouts_Check In Time"
  ],
  "column_metadata": {
    "Patrons_First Name": {
      "alias": "Patrons_First Name",
      "display_name": "Patrons_First Name",
      "type": "text",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "First Name",
      "input_table_name": "Patrons",
      "input_table_id": 21,
      "input_alias": null
    },
    "Patrons_Last Name": {
      "alias": "Patrons_Last Name",
      "display_name": "Patrons_Last Name",
      "type": "text",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "Last Name",
      "input_table_name": "Patrons",
      "input_table_id": 21,
      "input_alias": null
    },
    "Patrons_Email": {
      "alias": "Patrons_Email",
      "display_name": "Patrons_Email",
      "type": "mathesar_types.email",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "Email",
      "input_table_name": "Patrons",
      "input_table_id": 21,
      "input_alias": null
    },
    "Books_Title": {
      "alias": "Books_Title",
      "display_name": "Books_Title",
      "type": "text",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "Title",
      "input_table_name": "Books",
      "input_table_id": 13,
      "input_alias": null
    },
    "Checkouts_Checkout Time": {
      "alias": "Checkouts_Checkout Time",
      "display_name": "Checkouts_Checkout Time",
      "type": "timestamp with time zone",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "Checkout Time",
      "input_table_name": "Checkouts",
      "input_table_id": 33,
      "input_alias": null
    },
    "Checkouts_Due Date": {
      "alias": "Checkouts_Due Date",
      "display_name": "Checkouts_Due Date",
      "type": "date",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "Due Date",
      "input_table_name": "Checkouts",
      "input_table_id": 33,
      "input_alias": null
    },
    "Checkouts_Check In Time": {
      "alias": "Checkouts_Check In Time",
      "display_name": "Checkouts_Check In Time",
      "type": "timestamp without time zone",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "Check In Time",
      "input_table_name": "Checkouts",
      "input_table_id": 33,
      "input_alias": null
    }
  },
  "parameters": {
    "limit": 100,
    "offset": 0
  }
}

The response with summarizing to a list:

{
  "query": {
    "schema": 2,
    "initial_columns": [
      {
        "alias": "Patrons_First Name",
        "id": 110
      },
      {
        "alias": "Patrons_Last Name",
        "id": 111
      },
      {
        "alias": "Patrons_Email",
        "id": 112
      },
      {
        "alias": "Books_Title",
        "id": 2,
        "jp_path": [
          [
            109,
            290
          ],
          [
            266,
            1
          ],
          [
            79,
            86
          ]
        ]
      },
      {
        "alias": "Checkouts_Checkout Time",
        "id": 363,
        "jp_path": [
          [
            109,
            290
          ]
        ]
      },
      {
        "alias": "Checkouts_Due Date",
        "id": 389,
        "jp_path": [
          [
            109,
            290
          ]
        ]
      },
      {
        "alias": "Checkouts_Check In Time",
        "id": 416,
        "jp_path": [
          [
            109,
            290
          ]
        ]
      }
    ],
    "transformations": [
      {
        "type": "filter",
        "spec": {
          "lesser": [
            {
              "column_name": [
                "Checkouts_Due Date"
              ]
            },
            {
              "literal": [
                "2023-09-18"
              ]
            }
          ]
        }
      },
      {
        "type": "filter",
        "spec": {
          "null": [
            {
              "column_name": [
                "Checkouts_Check In Time"
              ]
            }
          ]
        }
      },
      {
        "type": "summarize",
        "spec": {
          "base_grouping_column": "Patrons_Email",
          "grouping_expressions": [
            {
              "input_alias": "Patrons_Email",
              "output_alias": "Patrons_Email_grouped"
            },
            {
              "input_alias": "Patrons_First Name",
              "output_alias": "Patrons_First Name_group"
            },
            {
              "input_alias": "Patrons_Last Name",
              "output_alias": "Patrons_Last Name_group"
            }
          ],
          "aggregation_expressions": [
            {
              "input_alias": "Books_Title",
              "output_alias": "Books_Title_agged",
              "function": "distinct_aggregate_to_array"
            },
            {
              "input_alias": "Checkouts_Checkout Time",
              "output_alias": "Checkouts_Checkout Time_agged",
              "function": "distinct_aggregate_to_array"
            },
            {
              "input_alias": "Checkouts_Due Date",
              "output_alias": "Checkouts_Due Date_agged",
              "function": "distinct_aggregate_to_array"
            },
            {
              "input_alias": "Checkouts_Check In Time",
              "output_alias": "Checkouts_Check In Time_agged",
              "function": "distinct_aggregate_to_array"
            }
          ]
        }
      }
    ],
    "base_table": 21,
    "display_names": {
      "Patrons_Email_grouped": "Patrons_Email group",
      "Patrons_First Name_group": "Patrons_First Name group",
      "Patrons_Last Name_group": "Patrons_Last Name group",
      "Books_Title_agged": "Books_Title distinct list",
      "Checkouts_Checkout Time_agged": "Checkouts_Checkout Time distinct list",
      "Checkouts_Due Date_agged": "Checkouts_Due Date distinct list",
      "Checkouts_Check In Time_agged": "Checkouts_Check In Time distinct list",
      "Patrons_First Name": "Patrons_First Name",
      "Patrons_Last Name": "Patrons_Last Name",
      "Patrons_Email": "Patrons_Email",
      "Books_Title": "Books_Title",
      "Checkouts_Checkout Time": "Checkouts_Checkout Time",
      "Checkouts_Due Date": "Checkouts_Due Date",
      "Checkouts_Check In Time": "Checkouts_Check In Time"
    }
  },
  "records": {
    "count": 1,
    "grouping": null,
    "preview_data": null,
    "results": [
      {
        "Patrons_Email_grouped": "athompson76@stein.com",
        "Patrons_First Name_group": "Alice",
        "Patrons_Last Name_group": "Thompson",
        "Books_Title_agged": [
          "A Borrowed Man",
          "Black Powder War"
        ],
        "Checkouts_Checkout Time_agged": [
          "2023-08-28T04:00:00Z",
          "2023-08-28T06:18:03.062962Z"
        ],
        "Checkouts_Due Date_agged": [
          "2023-09-11"
        ],
        "Checkouts_Check In Time_agged": [
          null
        ]
      }
    ]
  },
  "output_columns": [
    "Patrons_Email_grouped",
    "Patrons_First Name_group",
    "Patrons_Last Name_group",
    "Books_Title_agged",
    "Checkouts_Checkout Time_agged",
    "Checkouts_Due Date_agged",
    "Checkouts_Check In Time_agged"
  ],
  "column_metadata": {
    "Patrons_First Name": {
      "alias": "Patrons_First Name",
      "display_name": "Patrons_First Name",
      "type": "text",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "First Name",
      "input_table_name": "Patrons",
      "input_table_id": 21,
      "input_alias": null
    },
    "Patrons_Last Name": {
      "alias": "Patrons_Last Name",
      "display_name": "Patrons_Last Name",
      "type": "text",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "Last Name",
      "input_table_name": "Patrons",
      "input_table_id": 21,
      "input_alias": null
    },
    "Patrons_Email": {
      "alias": "Patrons_Email",
      "display_name": "Patrons_Email",
      "type": "mathesar_types.email",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "Email",
      "input_table_name": "Patrons",
      "input_table_id": 21,
      "input_alias": null
    },
    "Books_Title": {
      "alias": "Books_Title",
      "display_name": "Books_Title",
      "type": "text",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "Title",
      "input_table_name": "Books",
      "input_table_id": 13,
      "input_alias": null
    },
    "Checkouts_Checkout Time": {
      "alias": "Checkouts_Checkout Time",
      "display_name": "Checkouts_Checkout Time",
      "type": "timestamp with time zone",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "Checkout Time",
      "input_table_name": "Checkouts",
      "input_table_id": 33,
      "input_alias": null
    },
    "Checkouts_Due Date": {
      "alias": "Checkouts_Due Date",
      "display_name": "Checkouts_Due Date",
      "type": "date",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "Due Date",
      "input_table_name": "Checkouts",
      "input_table_id": 33,
      "input_alias": null
    },
    "Checkouts_Check In Time": {
      "alias": "Checkouts_Check In Time",
      "display_name": "Checkouts_Check In Time",
      "type": "timestamp without time zone",
      "type_options": null,
      "display_options": null,
      "is_initial_column": true,
      "input_column_name": "Check In Time",
      "input_table_name": "Checkouts",
      "input_table_id": 33,
      "input_alias": null
    },
    "Patrons_Email_grouped": {
      "alias": "Patrons_Email_grouped",
      "display_name": "Patrons_Email group",
      "type": "mathesar_types.email",
      "type_options": null,
      "display_options": null,
      "is_initial_column": false,
      "input_column_name": null,
      "input_table_name": null,
      "input_table_id": null,
      "input_alias": "Patrons_Email"
    },
    "Patrons_First Name_group": {
      "alias": "Patrons_First Name_group",
      "display_name": "Patrons_First Name group",
      "type": "text",
      "type_options": null,
      "display_options": null,
      "is_initial_column": false,
      "input_column_name": null,
      "input_table_name": null,
      "input_table_id": null,
      "input_alias": "Patrons_First Name"
    },
    "Patrons_Last Name_group": {
      "alias": "Patrons_Last Name_group",
      "display_name": "Patrons_Last Name group",
      "type": "text",
      "type_options": null,
      "display_options": null,
      "is_initial_column": false,
      "input_column_name": null,
      "input_table_name": null,
      "input_table_id": null,
      "input_alias": "Patrons_Last Name"
    },
    "Books_Title_agged": {
      "alias": "Books_Title_agged",
      "display_name": "Books_Title distinct list",
      "type": "_array",
      "type_options": {
        "item_type": "text"
      },
      "display_options": null,
      "is_initial_column": false,
      "input_column_name": null,
      "input_table_name": null,
      "input_table_id": null,
      "input_alias": "Books_Title"
    },
    "Checkouts_Checkout Time_agged": {
      "alias": "Checkouts_Checkout Time_agged",
      "display_name": "Checkouts_Checkout Time distinct list",
      "type": "_array",
      "type_options": {
        "item_type": "timestamp with time zone"
      },
      "display_options": null,
      "is_initial_column": false,
      "input_column_name": null,
      "input_table_name": null,
      "input_table_id": null,
      "input_alias": "Checkouts_Checkout Time"
    },
    "Checkouts_Due Date_agged": {
      "alias": "Checkouts_Due Date_agged",
      "display_name": "Checkouts_Due Date distinct list",
      "type": "_array",
      "type_options": {
        "item_type": "date"
      },
      "display_options": null,
      "is_initial_column": false,
      "input_column_name": null,
      "input_table_name": null,
      "input_table_id": null,
      "input_alias": "Checkouts_Due Date"
    },
    "Checkouts_Check In Time_agged": {
      "alias": "Checkouts_Check In Time_agged",
      "display_name": "Checkouts_Check In Time distinct list",
      "type": "_array",
      "type_options": {
        "item_type": "timestamp without time zone"
      },
      "display_options": null,
      "is_initial_column": false,
      "input_column_name": null,
      "input_table_name": null,
      "input_table_id": null,
      "input_alias": "Checkouts_Check In Time"
    }
  },
  "parameters": {
    "limit": 100,
    "offset": 0
  }
}
mathemancer commented 1 year ago

I wouldn't have noticed, but it actually behaves differently within the same array, making the problem especially disconcerting:

image

The first value is reported unformatted, and in UTC; the second is formatted, and in my local time.

pavish commented 1 year ago

The API should return the same value after summarizing to a list that it does without summarizing, and

We'd have to agree on a specific ISO standard format for the API requests. I don't think we follow one strictly.

The Front End should not break when it receives a timestamp without decimal places.

I don't think we should categorize this behaviour as the frontend breaking, since if the FE isn't able to parse a date, it simply displays the response from the server. Having a standard format should help here.

mathemancer commented 1 year ago

The Front End should not break when it receives a timestamp without decimal places.

I don't think we should categorize this behaviour as the frontend breaking, since if the FE isn't able to parse a date, it simply displays the response from the server. Having a standard format should help here.

I mean, the value that's not being handled is an ISO8601-compliant timestamp. Wouldn't it be best for

Otherwise, we'll have a brittle tight coupling between the back end and front end.