Cast to date fails if date is in DD-MM-YY format

[Anish9901]

Description

Expected behavior

Casting shouldn't fail

To Reproduce

1. Create a table from scratch
2. Create a Text column named "Date"
3. Add 22/06/24 as a record in the date column.
4. Try casting the Date column to DATE type.

See also

• Parse user entry of dates on the front end #2327

[kgodey]

@Anish9901 is this a regression?

[Anish9901]

No @kgodey, this is not a regression, we can set different datestyles on postgres at runtime but there is no way to do it via the UI at the moment.

[seancolsen]

@Anish9901 if a text column contained one cell with the string "01/02/03", how would you expect Mathesar to cast it? Would it become January 2nd or February 1st? Or would the user (or potentially Mathesar administrator) be granted some control over this behavior? While much of the world might expect the date to be in February, US users would likely expect it to be January.

[Anish9901]

@seancolsen Ideally, I'd like the date format to be inferred based on the browser locale. However, I can see where that could be a problem. A better solution might be to allow specifying the date format during the casting process, similar to how we allow users to choose between different date formats once the date column is already created.

Nonetheless, I think we'll need a team-wide discussion to get to a best possible solution for this problem hence the label needs: requirements.

[mathemancer]

First, I don't think this should be in the beta milestone.

We're likely to lose functionality if we're not careful about modifying this behavior, since the Postgres date casting behavior is pretty much as good as possible, subject to the issue that @seancolsen raised w.r.t. locale differences. For example, the following are all correctly handled:

• mathesar=# SELECT '2001-12-15'::date;
• mathesar=# SELECT '2001/12/15'::date;
• mathesar=# SELECT '20011215'::date;
• mathesar=# SELECT '12-15-2001'::date; -- U.S. locale assumed
• mathesar=# SELECT '12/15/2001'::date; -- U.S. locale assumed
• mathesar=# SELECT '12/1/2001'::date; -- U.S. locale assumed
• mathesar=# SELECT '1/1/2001'::date; -- U.S. locale assumed
• mathesar=# SELECT '1.1.2001'::date; -- U.S. locale assumed
• mathesar=# SELECT '12/15/2001 BC'::date; -- U.S. locale assumed
• mathesar=# SELECT 'J2452259'::date; -- Julian date
• mathesar=# SELECT '0034-12-15 BC'::date;
• mathesar=# SELECT 'December 15 2001 BC'::date;
• mathesar=# SELECT 'Tuesday, October 29, 2024'::date;
• mathesar=# SELECT 'Tuesday, October 29, 2024 8:42 AM'::date;

I could go on, but I assume the point is made. We are simply not going to do better than that with home-rolled logic. Moreover, I don't think it makes any sense to give up that power or flexibility by making the user choose a format that has to apply to an entire column (which could be from an inconsistent source), and certainly not with a picker that's likely to lack many (or most) of the options available out-of-the-box in Postgres.

With all that said, the problem is real. I suggest a dropdown that simply allows the user to choose the DateStyle parameter if desired (with some helpful hints). See the docs for what that would look like. The short version is that the parameter lets you specify how Postgres should choose between indeterminate options for, e.g., mathesar=# SELECT '1/1/24'::date; without constraining the behavior to a specific format.

It's possible to make this setting on a per-session or per-transaction basis. I suggest per-transaction, with the setting submitted as an optional parameter when calling the cast function. We shouldn't change the response format, since that's already handled properly.