Handle TIMESTAMP data type in the backend

[kgodey]

This issue is to ensure that Mathesar can handle the Postgres TIMESTAMP data type.

As part of this issue, we need to ensure that:

• Users can use the API to change a column to TIMESTAMP(with time zone) if it's possible to do so.
• Users can use the API to change a column to TIMESTAMP(without time zone) if it's possible to do so.
• Users can set and change the precision of a given TIMESTAMP column via a type_options field in the API.
• Automatic type inference during file import suggests TIMESTAMP when it makes sense to do so.
  • We could use something like https://dateparser.readthedocs.io/

Additional Context

• Implementation for date & time types #250 (parent issue)
• See PR Extend the v0/databases/'s types list to show both Mathesar and DB type information. #368 for code that calculates which types are allowed for a column.
• This issue was previously specced out in Controlled casting for dates, times, and timestamps #128

[silentninja]

Timestamps data type cause a conflict with date type when inferring a date without time value as date allows lossy coercion, same with timestamp being able to accept a date without time. We can solve this problem by restricting only if the data contains exact details(date and time mandatory for timestamp).

There are two ways I can think of:

1. Restrict inference to timestamps - When inferring, we don't consider date type, instead we always infer any date(with or without time) as a timestamp.
2. Throw up an error when casting with inappropriate data - If data do not confer exactly to the data type(missing date or time for a timestamp or contains time for date type) we throw an error. We end up opinionated with this approach and restrict the user who would intentionally want to cast date to a timestamp.
3. Add strict parameter to casting function - This extends upon the previous suggestion(Option 2) by adding a strict parameter. We enable strict casting only when inferring, but allow lossy cast when done manually.

@mathemancer @kgodey @dmos62 Would like your opinion on this issue.

[dmos62]

For context (probably just for me), Postgres date contains date (no time of day) information; Postgres time contains time of day (no date) information; Postgres timestamp contains date and time information. If we have date+time information, treating it as timestamp should be first priority. If we have only time or date information treating it as time and date respectively should be the priority. We can do inaccurate casting as well (date to timestamp), but not by default.

So this sounds like option 3.

A quick note on terminology, casting a timestamp from a date is not lossy, since information is not being lost. It's sort of the reverse where you're providing more information than contained in the date type (i.e. time of day information). Though, I guess if you're guessing the time of day, you could say that you're losing the knowledge that you didn't have time of day originally.

[silentninja]

casting a timestamp from a date is not lossy,

By lossy I mean there is no way to get back the data once it has been coerced. For example

SELECT '1997-12-17 07:37:16-08'::DATE::TIMESTAMP;
1997-12-17 00:00:00.000000

The time data is lost upon downcasting to date and upcasting to a timestamp.

[mathemancer]

I've already been thinking along the lines of option (3) above. I think we're eventually going to need to have different casting functions based on whether we're trying to infer a type or not. For now, I suggest using a custom function for casting that disallows lossy conversion (see the one for casting to integer, for example). We already have an issue #402 involving setting up default casting behavior when appropriate. Perhaps that could be the first step to an "advanced mode" that would allow lossy casting.

[kgodey]

Option 3 sounds fine to me. To confirm, this means that if we see both date and time information, we would infer it to be TIMESTAMP and only date information would be a DATE, correct?

[silentninja]

@kgodey Yes, that's right.