Try more date and time formats when parsing temporal values on the front end

[seancolsen]

Steps to reproduce

1. Set up a table like this:

   

   Verify all UI matches with the arrows.

2. Open your browser dev tools network tab.

3. Copy the string:

   2000-01-01 09:00:00
   

4. Edit the date_time cell. While in edit mode, paste the string and press Enter to save.

5. In your dev tools, observe that Mathesar sends something like the following:

   2000-01-01 09:00:00 -05:00
   

   That -05:00 bit at the end is the timezone, as determined by your browser. It might be different for you depending on what timezone you're in.

   This is good. The front end doesn't just tack this on at the end. The front end actually parses your entire date entry and then reformats it in a format that PostgreSQL can interpret unambiguously.

6. Add a new record and edit the date_time cell, this time pasting the following value:

   2000-01-01 9:00:00
   

   Notice that the only thing we've change here is that we've removed the leading 0 before the 9.

7. This time, observe that Mathesar sends exactly the string you entered.

8. And (unless you're in UTC timezone) PostgreSQL will actually send back will be a different time, making the displayed cell different from you you entered. This is bad.

What's going on (and how to fix it!)

1. On the front end, find the DateTimeFormatter class, and look at the parse method. You can see some code comments describing how this logic works (and why).

2. We need to make the parseWithSpec free function more robust so as to handle input like the format in the above repro steps. The bulk of your changes will probably end up being in DateTimeSpecification.ts.

3. You can tighten your feedback loop on this parsing function by adding so console logging in DateTimeFormatter.parse and observing the output as you type into the input on the record page:

   

4. It would be good to try to think of additional date/time formats that we can parse in addition to the one listed above.

Additional context

• This bug was originally described in Maddening behavior when entering data into "TIMESTAMP WITH TIME ZONE" column #4911

[NormannLaw]

Hi Sean, could you assign this to me? Thanks!