Design for date & time data types

[kgodey]

Problem

Users will want to store dates and times. We should allow them to do so and provide an intuitive user experience for them.

Proposed solution

We need a design for the following functionality for the date and time data types:

• Displaying columns of this type in tables
• Creating a new column with this data type
• Changing an existing column to this data type (including error states)
• Showing non-technical users a friendly name for this data type. (The underlying Postgres data type could be DATE, TIME, or TIMESTAMP, with or without timezone).

Options

Columns of this type will have the following options:

Specific to this Type

• Whether to store date
• Whether to store time
• Whether to store timezone, and which timezone

We should make sure to present these options in a way that's helpful to people who know what they're doing and that non-technical people can ignore.

Common to all Types

• Default value (this will be the default value of the column if the user doesn't enter anything)
• Allow empty values (whether the column is nullable)
• Unique (whether the values in the column have to be unique, validated at the DB level)

Grouping

Columns of this type will be able to grouped by any of the options provided by the PostgreSQL EXTRACT function. If it's only a date or only a time, we should restrict the options accordingly (e.g. don't group by month if the field only stores times and not dates).

We need to figure out how to show the appropriate ranges based on the data. e.g. if the data is all dates in 2008, group by century doesn't make sense.

Filtering

Columns of this type will support the following filters:

• between
• is
• is not
• before
• after
• on or before
• on or after
• is empty
• is not empty

The filter value should support different date formats as well as natural language like "next month", or "tomorrow".

Additional Context

• We should support different date formats. Should we automatically figure this out by locale or have display options?

[ghislaineguerin]

Specs posted on https://wiki.mathesar.org/en/design/specs/date-time-data-type