Skip to content

Support ANSI SQL datetime / interval literals #62

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

Closed
nickolay opened this issue May 3, 2019 · 2 comments
Closed

Support ANSI SQL datetime / interval literals #62

nickolay opened this issue May 3, 2019 · 2 comments

Comments

@nickolay
Copy link
Contributor

nickolay commented May 3, 2019

See:

An open question is whether the date/time values should store the string values intact or parse the strings and store them as chrono values as in the code I removed in https://github.com/andygrove/sqlparser-rs/pull/61 (andygrove@de177f1) - I'm leaning towards the former, so that a user of this library can choose the preferred representation.

nickolay referenced this issue May 21, 2019
1) Removed unused date/time parsing methods from `Parser`

I don't see how the token-based parsing code would ever be used: the
date/time literals are usually quoted, like `DATE 'yyyy-mm-dd'` or
simply `'YYYYMMDD'`, so the date will be a single token.

2) Removed unused date/time related variants from `Value` and the
dependency on `chrono`.

We don't support parsing date/time literals at the moment and when we
do I think we should store the exact String to let the consumer parse
it as they see fit.

3) Removed `parse_timestamps_example` and
`parse_timestamps_with_millis_example` tests. They parsed as
`number(2016) minus number(02) minus number(15) <END OF EXPRESSION>`
(leaving the time part unparsed) as it makes no sense to try parsing
a yyyy-mm-dd value as an SQL expression.
@nickolay
Copy link
Contributor Author

nickolay commented Jun 9, 2019

Some notes on INTERVALs in SQL:

While reading through the grammar rules from the spec and the explanation, I made this cheatsheet for myself:

<interval literal> ::= INTERVAL [ <sign> ] '<value>' <interval qualifier>
<interval type> ::= INTERVAL <interval qualifier>

<interval qualifier> ::=
    SECOND ( <leading precision>, <fractional seconds precision> )
  | <start field> [ ( <leading field precision> ) ]
      [ TO <end field> [ ( <fractional seconds precision> ) ] ]

where <start field> is one of YEAR/MONTH/DAY/HOUR/MINUTE and <end field> is either one of those as well, or SECOND. <start field> is supposed to be a unit that's not smaller than <end field> (although Postgres forbids YEAR TO YEAR in my testing.)

The <start field> and <end field> define the fields expected in the <value>, which follows the format:

  • 'yy-mm' for YEAR TO MONTH intervals (or simply 'yy' for YEAR and 'mm' for MONTH intervals)
  • 'dd hh:mi:ss.nn' for DAY TO SECOND, or the corresponding subset of that for DAY TO HOUR/MINUTE, HOUR TO MINUTE/SECOND, MINUTE TO SECOND, and DAY/HOUR/MINUTE/SECOND intervals.

The plus/minus sign is also allowed inside the quoted <value>.

(Posgres allows omitting <interval qualifier> in exchange for using more complex value string: INTERVAL '2 years 13 months'.)

The <leading field precision> is the number of digits allowed in the "start field" of the interval, the <fractional seconds precision> is the number of digits allowed in the fractional part the "end field" (but only if the end field is seconds). There's no need to specify precision for other fields, as you're not allowed to write something like '0:120' HOUR TO MINUTE or '33.5' MINUTE (while you can write '1000:0' HOUR TO MINUTE).

@nickolay
Copy link
Contributor Author

nickolay commented Jun 9, 2019

Thanks to all the hard work by @benesch we now have support for DATE/TIME/INTERVAL literals, with the following caveats:

@nickolay nickolay closed this as completed Jun 9, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant