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

Timezone offset in timestamp/time literal, CAST, SET TIME ZONE and AT TIME ZONE should follow SQL standard syntax only [CORE6429] #6666

Closed
firebird-issue-importer opened this issue Oct 25, 2020 · 10 comments

Comments

@firebird-issue-importer

Submitted by: @mrotteveel

Currently the syntax of a timezone offset in a timestamp/time literal, CAST, SET TIME ZONE and AT TIME ZONE is a bit too flexible, allowing [+|-][H...]H[:[M...]M], so 2, 2:0, 02:00, 2:00 and 02:00 (and variations with +) all are +02:00, and 2:1, 02:1, 2:01, 02:01 (and variations with +), all are +02:01. It also allows things like 0002, 000002:00001, etc

As this is a new feature, I would prefer that we take a conservative approach and use the more specific and less ambiguous syntax of the SQL standard (in 5.3 <literal>):

<time zone interval> ::= <sign> <hours value> <colon> <minutes value>
<hours value> ::= <datetime value>
<minutes value> ::= <datetime value>
<datetime value> ::= <unsigned integer>
<sign> ::= <plus sign> | <minus sign>

Combined with:

31) Within a <datetime literal>, the <years value> shall contain four digits. The <seconds integer value> and
other datetime components, with the exception of <seconds fraction>, shall each contain two digits.

In other words, the format should be: {+|-}HH:MM (so, no optional components, always 2 digits for hours and minutes)

This means that the of the offsets shown earlier, only +02:00 and +02:01 would be valid

Commits: ff37d44

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 25, 2020

Modified by: @mrotteveel

description: Currently the syntax of a timezone offset in a timestamp or time literal is a bit too flexible (allowing [+|-][H]H[:[M]M], so 2, 2:0, 02:00, 2:00 and 02:00 (and variations with +) all are +02:00, and 2:1, 02:1, 2:01, 02:01 (and variations with +), all are +02:01.

As this is a new feature, I would prefer that we take a conservative approach and use the more specific and less ambiguous syntax of the SQL standard (in 5.3 <literal>):

<time zone interval> ::= <sign> <hours value> <colon> <minutes value>
<hours value> ::= <datetime value>
<minutes value> ::= <datetime value>
<datetime value> ::= <unsigned integer>
<sign> ::= <plus sign> | <minus sign>

Combined with:

31) Within a <datetime literal>, the <years value> shall contain four digits. The <seconds integer value> and
other datetime components, with the exception of <seconds fraction>, shall each contain two digits.

In other words, the format should be: {+|-}HH:MM (so, no optional components, always 2 digits for hours and minutes)

This means that the of the offsets shown earlier, only +02:00 and +02:01 would be valid

=>

Currently the syntax of a timezone offset in a timestamp/time literal, CAST, SET TIME ZONE and AT TIME ZONE is a bit too flexible (allowing [+|-][H]H[:[M]M], so 2, 2:0, 02:00, 2:00 and 02:00 (and variations with +) all are +02:00, and 2:1, 02:1, 2:01, 02:01 (and variations with +), all are +02:01.

As this is a new feature, I would prefer that we take a conservative approach and use the more specific and less ambiguous syntax of the SQL standard (in 5.3 <literal>):

<time zone interval> ::= <sign> <hours value> <colon> <minutes value>
<hours value> ::= <datetime value>
<minutes value> ::= <datetime value>
<datetime value> ::= <unsigned integer>
<sign> ::= <plus sign> | <minus sign>

Combined with:

31) Within a <datetime literal>, the <years value> shall contain four digits. The <seconds integer value> and
other datetime components, with the exception of <seconds fraction>, shall each contain two digits.

In other words, the format should be: {+|-}HH:MM (so, no optional components, always 2 digits for hours and minutes)

This means that the of the offsets shown earlier, only +02:00 and +02:01 would be valid

summary: Timezone offset in timestamp/time literal and CAST should follow SQL standard syntax only => Timezone offset in timestamp/time literal, CAST, SET TIME ZONE and AT TIME ZONE should follow SQL standard syntax only

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 25, 2020

Modified by: @mrotteveel

description: Currently the syntax of a timezone offset in a timestamp/time literal, CAST, SET TIME ZONE and AT TIME ZONE is a bit too flexible (allowing [+|-][H]H[:[M]M], so 2, 2:0, 02:00, 2:00 and 02:00 (and variations with +) all are +02:00, and 2:1, 02:1, 2:01, 02:01 (and variations with +), all are +02:01.

As this is a new feature, I would prefer that we take a conservative approach and use the more specific and less ambiguous syntax of the SQL standard (in 5.3 <literal>):

<time zone interval> ::= <sign> <hours value> <colon> <minutes value>
<hours value> ::= <datetime value>
<minutes value> ::= <datetime value>
<datetime value> ::= <unsigned integer>
<sign> ::= <plus sign> | <minus sign>

Combined with:

31) Within a <datetime literal>, the <years value> shall contain four digits. The <seconds integer value> and
other datetime components, with the exception of <seconds fraction>, shall each contain two digits.

In other words, the format should be: {+|-}HH:MM (so, no optional components, always 2 digits for hours and minutes)

This means that the of the offsets shown earlier, only +02:00 and +02:01 would be valid

=>

Currently the syntax of a timezone offset in a timestamp/time literal, CAST, SET TIME ZONE and AT TIME ZONE is a bit too flexible, allowing [+|-][H]H[:[M]M], so 2, 2:0, 02:00, 2:00 and 02:00 (and variations with +) all are +02:00, and 2:1, 02:1, 2:01, 02:01 (and variations with +), all are +02:01.

As this is a new feature, I would prefer that we take a conservative approach and use the more specific and less ambiguous syntax of the SQL standard (in 5.3 <literal>):

<time zone interval> ::= <sign> <hours value> <colon> <minutes value>
<hours value> ::= <datetime value>
<minutes value> ::= <datetime value>
<datetime value> ::= <unsigned integer>
<sign> ::= <plus sign> | <minus sign>

Combined with:

31) Within a <datetime literal>, the <years value> shall contain four digits. The <seconds integer value> and
other datetime components, with the exception of <seconds fraction>, shall each contain two digits.

In other words, the format should be: {+|-}HH:MM (so, no optional components, always 2 digits for hours and minutes)

This means that the of the offsets shown earlier, only +02:00 and +02:01 would be valid

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 25, 2020

Modified by: @mrotteveel

description: Currently the syntax of a timezone offset in a timestamp/time literal, CAST, SET TIME ZONE and AT TIME ZONE is a bit too flexible, allowing [+|-][H]H[:[M]M], so 2, 2:0, 02:00, 2:00 and 02:00 (and variations with +) all are +02:00, and 2:1, 02:1, 2:01, 02:01 (and variations with +), all are +02:01.

As this is a new feature, I would prefer that we take a conservative approach and use the more specific and less ambiguous syntax of the SQL standard (in 5.3 <literal>):

<time zone interval> ::= <sign> <hours value> <colon> <minutes value>
<hours value> ::= <datetime value>
<minutes value> ::= <datetime value>
<datetime value> ::= <unsigned integer>
<sign> ::= <plus sign> | <minus sign>

Combined with:

31) Within a <datetime literal>, the <years value> shall contain four digits. The <seconds integer value> and
other datetime components, with the exception of <seconds fraction>, shall each contain two digits.

In other words, the format should be: {+|-}HH:MM (so, no optional components, always 2 digits for hours and minutes)

This means that the of the offsets shown earlier, only +02:00 and +02:01 would be valid

=>

Currently the syntax of a timezone offset in a timestamp/time literal, CAST, SET TIME ZONE and AT TIME ZONE is a bit too flexible, allowing [+|-][H]H[:[M]M], so 2, 2:0, 02:00, 2:00 and 02:00 (and variations with +) all are +02:00, and 2:1, 02:1, 2:01, 02:01 (and variations with +), all are +02:01. It also allows things like 0002, 000002:00001, etc

As this is a new feature, I would prefer that we take a conservative approach and use the more specific and less ambiguous syntax of the SQL standard (in 5.3 <literal>):

<time zone interval> ::= <sign> <hours value> <colon> <minutes value>
<hours value> ::= <datetime value>
<minutes value> ::= <datetime value>
<datetime value> ::= <unsigned integer>
<sign> ::= <plus sign> | <minus sign>

Combined with:

31) Within a <datetime literal>, the <years value> shall contain four digits. The <seconds integer value> and
other datetime components, with the exception of <seconds fraction>, shall each contain two digits.

In other words, the format should be: {+|-}HH:MM (so, no optional components, always 2 digits for hours and minutes)

This means that the of the offsets shown earlier, only +02:00 and +02:01 would be valid

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 25, 2020

Modified by: @mrotteveel

description: Currently the syntax of a timezone offset in a timestamp/time literal, CAST, SET TIME ZONE and AT TIME ZONE is a bit too flexible, allowing [+|-][H]H[:[M]M], so 2, 2:0, 02:00, 2:00 and 02:00 (and variations with +) all are +02:00, and 2:1, 02:1, 2:01, 02:01 (and variations with +), all are +02:01. It also allows things like 0002, 000002:00001, etc

As this is a new feature, I would prefer that we take a conservative approach and use the more specific and less ambiguous syntax of the SQL standard (in 5.3 <literal>):

<time zone interval> ::= <sign> <hours value> <colon> <minutes value>
<hours value> ::= <datetime value>
<minutes value> ::= <datetime value>
<datetime value> ::= <unsigned integer>
<sign> ::= <plus sign> | <minus sign>

Combined with:

31) Within a <datetime literal>, the <years value> shall contain four digits. The <seconds integer value> and
other datetime components, with the exception of <seconds fraction>, shall each contain two digits.

In other words, the format should be: {+|-}HH:MM (so, no optional components, always 2 digits for hours and minutes)

This means that the of the offsets shown earlier, only +02:00 and +02:01 would be valid

=>

Currently the syntax of a timezone offset in a timestamp/time literal, CAST, SET TIME ZONE and AT TIME ZONE is a bit too flexible, allowing [+|-][H...]H[:[M...]M], so 2, 2:0, 02:00, 2:00 and 02:00 (and variations with +) all are +02:00, and 2:1, 02:1, 2:01, 02:01 (and variations with +), all are +02:01. It also allows things like 0002, 000002:00001, etc

As this is a new feature, I would prefer that we take a conservative approach and use the more specific and less ambiguous syntax of the SQL standard (in 5.3 <literal>):

<time zone interval> ::= <sign> <hours value> <colon> <minutes value>
<hours value> ::= <datetime value>
<minutes value> ::= <datetime value>
<datetime value> ::= <unsigned integer>
<sign> ::= <plus sign> | <minus sign>

Combined with:

31) Within a <datetime literal>, the <years value> shall contain four digits. The <seconds integer value> and
other datetime components, with the exception of <seconds fraction>, shall each contain two digits.

In other words, the format should be: {+|-}HH:MM (so, no optional components, always 2 digits for hours and minutes)

This means that the of the offsets shown earlier, only +02:00 and +02:01 would be valid

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 25, 2020

Commented by: @asfernandes

Regarding one digit H and M formats, I prefer to be consistent with Firebird itself instead the standard, and allow them.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 25, 2020

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 25, 2020

Commented by: @mrotteveel

I could live with single digit H, but single digit M is plain wrong. However, I prefer that we are strict now, and not continue the mess of ambiguous (or even plain wrong) syntax that Firebird has allowed in date/time/timestamp up to this point. Better to start strict, and relax later for well-argued reasons than to start with ambiguous or plain weird things (I mean: +10:3 looks more like a typo of +10:30 than of +10:03, but Firebird would interpret it as +10:03).

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 26, 2020

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 RC 1 [ 10930 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 28, 2020

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 28, 2020

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants