sql: support more values for DateStyle session parameter

[rafiss]

full docs: https://www.postgresql.org/docs/9.5/runtime-config-client.html#GUC-DATESTYLE

Sets the display format for date and time values, as well as the rules for interpreting ambiguous date input values. For historical reasons, this variable contains two independent components: the output format specification (ISO, Postgres, SQL, or German) and the input/output specification for year/month/day ordering (DMY, MDY, or YMD). These can be set separately or together. The keywords Euro and European are synonyms for DMY; the keywords US, NonEuro, and NonEuropean are synonyms for MDY. See Section 8.5 for more information. The built-in default is ISO, MDY, but initdb will initialize the configuration file with a setting that corresponds to the behavior of the chosen lc_time locale.

Epic CRDB-2508

Jira issue: CRDB-6339

[jjathman]

As another data point for this issue, we are attempting to use Oracle Golden Gate replication to move data from an existing Oracle DB to CockroachDB. OGG uses the Progress ODBC driver which appears to issue a SET DateStyle='iso, ymd' command when initiating a connection and isn't possible to disable. This appears to be a blocker for us that we can't work around.

[dbist]

we can't even pass datestyle as connection variable.

sh-4.4# cockroach sql --url 'postgresql://root@lb:26257?sslcert=%2Fcerts%2Fclient.root.crt&sslkey=%2Fcerts%2Fclient.root.key&sslmode=verify-full&sslrootcert=%2Fcerts%2Fca.crt&application_name=test&database=bank&datestyle=ISO, MDY'
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v20.2.5 (x86_64-unknown-linux-gnu, built 2021/02/16 12:52:58, go1.13.14) (same version as client)
# Cluster ID: 22c50ba7-1579-4b8b-8e8e-641a98b25f6e
#
# Enter \? for a brief introduction.
#
root@lb:26257/bank> \q
sh-4.4# cockroach sql --url 'postgresql://root@lb:26257?sslcert=%2Fcerts%2Fclient.root.crt&sslkey=%2Fcerts%2Fclient.root.key&sslmode=verify-full&sslrootcert=%2Fcerts%2Fca.crt&application_name=test&database=bank&datestyle=MDY, ISO'
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
ERROR: setting datestyle must be absent or ISO, MDY; got MDY, ISO
Failed running "sql"

[vy-ton]

@rafiss Can we revisit this for the May milestone?

[rafiss]

I hope that we can get started on it, but this will have the same complexities as described in #62313 (review) -- will need a potentially big refactor/new interfaces.

[otan]

I started working on a library to work across all styles (ISO,Postgres,SQL,German), but it turns out we only need the order components {MDY,YMD,DMY} to work maintaining the ISO style. That's a lot simpler, I'll be doing that instead.

If anyone wants to embark on this for future work, I've started that work here https://github.com/cockroachdb/pgdatetime. I have basically attempted to copy Postgres's parse logic, I have the tokens, formatting and some parsing logic working. But there are many road bumps to go:

• we need to support timezone parsing (sql: support timezone abbreviations in date parsing #31710). We can rely on time.ParseInLocation(loc, ...) then t.Location() to parse the timezone and translate these shorthands correctly to resolve the, e.g., IST ambiguity.
• support for infinity (urgh)
• lots of weird shortcuts (https://github.com/postgres/postgres/blob/0806d08d4626f0a71a4e83cb399ee965a8aac99b/src/interfaces/ecpg/pgtypeslib/dt_common.c#L20)
• lib/pq and jackc/pgx both rely on ISO to format time (e.g. https://github.com/lib/pq/blob/9e747ca50601fcb6c958dd89f4cb8aea3e067767/connector.go#L89-L96). This is because they take in the string and reparse it back into time assuming ISO style.

[otan]

Support for ISO / MDY,DMY,YMD is complete.
Other formats are not yet supported as they cannot be parsed, see previous comment.

[otan]

heads up stuff like

cockroach/pkg/sql/roleoption/role_option.go

Line 68 in d6b2ecb

VALIDUNTIL: `UPSERT INTO system.role_options (username, option, value) VALUES ($1, 'VALID UNTIL', $2::timestamptz::string)`,

needs to always needs to convert as ISO if we decide to support a non-ISO DateStyle.