Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

database/sql: Provide format string for SQL dates, times, and datetimes #30991

Closed
mcandre opened this issue Mar 21, 2019 · 4 comments
Closed

database/sql: Provide format string for SQL dates, times, and datetimes #30991

mcandre opened this issue Mar 21, 2019 · 4 comments

Comments

@mcandre
Copy link

@mcandre mcandre commented Mar 21, 2019

Go helpfully declares a predefined format string for parsing and formatting RFC3339 timestamps. Unfortunately, SQL uses a slightly different format, essentially replacing the T separator with a space. So, it would be helpful for the database/sql` package to provide format strings for reliably parsing and formatting SQL dates/times/datetimes. For example:

var SQLTimestampFormat = "2006-01-02 15:04:05"

Higher level API's supposedly offer automatic marshaling between SQL literals and Go objects, such as ORM's and special SQL driver options. However, sometimes lower level access is needed. In these cases, it is helpful to be able to use a predefined, tested format string rather than having to write our own.

@kardianos
Copy link
Contributor

@kardianos kardianos commented Mar 22, 2019

Which SQL databases use this format? Can you confirm it is widely supported?

@mcandre
Copy link
Author

@mcandre mcandre commented Mar 23, 2019

The ANSI SQL specification, PostgreSQL, MySQL/MariaDB, MSSQL, Oracle, BigQuery, Spanner, SQLite, MonetDB, Virtuoso, and Firebird support the aforementioned tr/$RFC3339_SEC_PRECISION_SANS_TZ/T/ / syntax as a common datetime format. Some implementations support an ISO 8601 variant with the conventional T separator, most expect a space. Some reject subsecond precision. Most reject a timezone.

We should probably bind this format string constant as AnsiSqlTimestampFormat or similar.

Another option is to add a method to the SQL driver interfaces for obtaining the driver's preferred date, time, and timestamp formats. The MySQL driver doesn't currently implement this, for example, but we could require them to in a future API.

DB2 appears to expects a dash, deviating from not only RFC3339 but ANSI SQL as well. C'est parfait merde.

@kardianos
Copy link
Contributor

@kardianos kardianos commented Mar 23, 2019

@mcandre Thanks for the info. (small nit it would be a const if added).

Some databases also support timezones such as timestampz or datetimeoffset. I have two additional question:

  1. What about date only, time of day, or timestampz (datetimeoffset)? Do you think those have the same utility? Are these also uniform across implementations? I think SQL Server supports offset only, but postgresql supports timezone names.
  2. These consts certainly do have value. Can you explain why you think they should be in the standard library rather then in a public package? Would this be useful for better conversion in database/sql?

Thanks.

@gopherbot
Copy link

@gopherbot gopherbot commented May 3, 2019

Timed out in state WaitingForInfo. Closing.

(I am just a bot, though. Please speak up if this is a mistake or you have the requested information.)

@gopherbot gopherbot closed this May 3, 2019
@golang golang locked and limited conversation to collaborators May 2, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
4 participants
You can’t perform that action at this time.