On 22-10-2020 13:09, Adriano dos Santos Fernandes wrote:
On 20/10/2020 08:42, Dmitry Yemanov wrote:
This is stil ambiguous.
This is valid in v3:
----
SQL> select timestamp '22 oct' from rdb$database;
CONSTANT
=========================
2020-10-22 00:00:00.0000
SQL> select timestamp '22 oct 20' from rdb$database;
CONSTANT
=========================
2020-10-22 00:00:00.0000
----
But now you can't easily know if 20 is a time zone or a year.
Neither of those are technically valid timestamp literals if you look at
the SQL standard*; the minimum valid literal for that timestamp would be
2020-10-22 00:00:00.
The problem is that Firebird is handling timestamp literals as a form of
casting, which it shouldn't do, and thus allows to full gamut of
ambiguous date formats Firebird allows in CAST(...) (which, BTW, it also
shouldn't do according to the standard). I would prefer if the timestamp
literal were restricted to what is defined in the SQL standard, but that
is a ship that has sailed.
In my opinion, the 20 in the string 22 oct 20 refers to the year, and
definitely not a time zone. Expecting a time zone IMHO only makes sense
if a time value has been specified, which should require at minimum the
hour and minutes to be specified as well.
On a related note, given time zones are new in Firebird 4, I think we
should be more strict about them and not introduce ambiguity in their
syntax, and we should follow the SQL standard requirements, and always
expect:
<sign> <hours value> <colon> <minutes value>
(which, to be clear, requires + or -, and both hours and minutes, just
having '20' should not be sufficient, that should be +20:00).
* The SQL standard defines the datetime literals as:
<sign> ::=
<plus sign>
| <minus sign>
<datetime literal> ::=
<date literal>
| <time literal>
| <timestamp literal>
<date literal> ::=
DATE <date string>
<time literal> ::=
TIME <time string>
<timestamp literal> ::=
TIMESTAMP <timestamp string>
<date string> ::=
<quote> <unquoted date string> <quote>
<time string> ::=
<quote> <unquoted time string> <quote>
<timestamp string> ::=
<quote> <unquoted timestamp string> <quote>
<time zone interval> ::=
<sign> <hours value> <colon> <minutes value>
<date value> ::=
<years value> <minus sign> <months value> <minus sign> <days value>
<time value> ::=
<hours value> <colon> <minutes value> <colon> <seconds value>
<unquoted date string> ::=
<date value>
<unquoted time string> ::=
<time value> [ <time zone interval> ]
<unquoted timestamp string> ::=
<unquoted date string> <space> <unquoted time string>
<years value> ::=
<datetime value>
<months value> ::=
<datetime value>
<days value> ::=
<datetime value>
<hours value> ::=
<datetime value>
<minutes value> ::=
<datetime value>
<seconds value> ::=
<seconds integer value> [ <period> [ <seconds fraction> ] ]
<seconds integer value> ::=
<unsigned integer>
<seconds fraction> ::=
<unsigned integer>
<datetime value> ::=
<unsigned integer>
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
