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

Jump to bottom

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
Labels
FrozenDueToAge WaitingForInfo Issue is not actionable because of missing required information, which needs to be provided.

Comments

@mcandre
Copy link

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

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

@mcandre
Copy link
Author

mcandre commented Mar 23, 2019 •
edited

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

@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.

@agnivade agnivade added the WaitingForInfo Issue is not actionable because of missing required information, which needs to be provided. label Apr 3, 2019
@gopherbot
Copy link

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.)

@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.
Assignees
No one assigned
Labels
FrozenDueToAge WaitingForInfo Issue is not actionable because of missing required information, which needs to be provided.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@mcandre @kardianos @agnivade @gopherbot