Hi Monty,
Michael Widenius wrote:
> Hi!
>
>>>>>> "Peter" == Peter Gulutzan <peter.gulut...@sun.com> writes:
>
> Peter> Hi all,
> Peter> On 01/15/2009 03:11 PM Peter Gulutzan wrote:
>
>>> For a TIME or DATETIME or TIMESTAMP literal, one can use
>>> '.' instead of ':' and one can skip leading fields. For example:
>>> INSERT INTO t (datetime_column) VALUES ('1001.01.01 11.22');
>>> For default MySQL changes the value to '1001-01-01 11:22:00'.
>>>
>>> The MySQL Reference Manual calls this "relaxed form".
>>> http://dev.mysql.com/doc/refman/5.1/en/using-date.html
>>>
>>> For WL#946 "TIME/TIMESTAMP/DATETIME with fractional seconds",
>>> '.' means something else: decimal point. The natural reading
>>> of '11.22' is going to be '11.22 seconds' for many people.
>>> The WL#946 HLS says "That [relaxed form] will no longer be
>>> possible, '.' must indicate that a fraction follows."
>>>
>>> I think we should consider these alternatives now:
>
>>> 1. Deprecate use of '.' as a substitute for standard
>>> punctuation characters. The other "relaxed form" stuff
>>> can remain. The manual should say "don't use '.' etc.".
>
> Peter> Roy says "it is not strictly necessary".
>
> Peter> Bernt says "I would go for 1) ...".
>
> Peter> Konstantin says "This should be OK".
>
> Peter> Unless more comments appear before January 31, this is the
> Peter> winning option. Trudy wrote guidelines in 2006
> Peter> https://inside.mysql.com/wiki/DeprecatingServerFeatures
> Peter> I will try to follow them, except that instead of getting
> Peter> approval from "architecture team" I will ask "ServerPT".
>
> As Dmitri pointed out, we shouldn't deprecate '.' as substitute for
> dates.
>
Yes, '.' is fine in a date. But how do we know it's in a date?
Answer: because all the fields are there. So this applies for 2) not 1).
> Another things is that we should stop making decisions about
> incompatible changes without listening to the MySQL users. They know
> more than we how MySQL is used and they are directly affected of any
> incompatible change we force upon them.
>
If you refer to the deprecation guidelines that I cited, you'll see
that there is a requirement to consult "interested parties (Support,
PS)" before submitting to a committee, etc.
>>> 2. Insist that '.' will continue to be a substitute for
>>> standard punctuation characters if any field is missing,
>>> but '.' will mean decimal if and only if all fields are
>>> present and have no substitutions. Thus '11.22' means
>>> "11 hours 22 minutes" but '00:00:11.22' means "11.22
>>> seconds".
>
> Peter> Roy said "option 2) will work quite well".
>
> The question here is how PostgreSQL and ANSI does this and also what
> is the logical interpretation of the number.
>
PostgreSQL accepts various forms but '.' can only mean decimal point.
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html
Bernt has answered re ANSI.
Roy has also answered re ANSI.
I doubt that there would be agreement about "logical interpretation".
> I think that if we go with 1), 11.22 should mean 11.22 seconds for a
> time field.
>
Yes, if we can't use '.' as a substitute for other punctuation
characters, then it's safe to have it in the place the
standard allows, as a decimal point. That's the point of 1).
And now for Dmitri's question, which was:
>AFAIK usage of '.' as a field separator in dates is quite wide spread
>in real world (although I am not not sure how often it is used in this
>role in SQL statements executed by our users).
>May be it makes sense to keep '.' as allowed separator in date part
>and prohibit it only in time part of datetime value?
Yes, provided it's possible to distinguish the date part and the
time part. That could be done by looking for ' ', or by using
one set of rules for DATE and another set of rules for TIME.
But I believe the essential idea is: when you know which field
it is by noting whether it's first / second / third / fourth / etc.
within the string, then you don't need to worry about choice of
punctuation character.
So '1.1.1 1.1.1.1' could be correctly interpreted as
'0001-01-01 01:01:01.1'. In fact that's happening now, already:
mysql> select cast('1-1-1 1.1.1.1' as datetime);
+-----------------------------------+
| cast('1-1-1 1.1.1.1' as datetime) |
+-----------------------------------+
| 0001-01-01 01:01:01.100000 |
+-----------------------------------+
1 row in set (0.01 sec)
> <cut>
>
>>> 3. Add a new mode, @@sql_mode=monty's_revenge. If it's on
>>> (which will never be the default), then "relaxed mode"
>>> is still possible (you can skip fields and you can use
>>> any punctuation other than '.'), but '.' means decimal
>>> point, so '11.22' means '11.22 seconds'.
>
> Peter> Roy said "it is not strictly necessary".
> Peter> Bernt said "with the addition of 3) for backwards compatability".
> Peter> Konstantin said "No new sql modes please".
>
> I agree with Konstantin that we should avoid new sql modes as much as
> possible. However, if we do break a lot of applications when we
> deprecate the usage of . as a separator for TIME fields, then we
> should add a temporary mode to help people move their applications
> forward until they have time to fix them.
If we deprecate, then, not only is there a consultation with multiple
parties, there is also a long period during which the users see warnings
in the code and in the manual. If you think that is not enough, then
you should say clearly that you favour 3), or that you favour some
combination, as Bernt does.
So far, the "winning option" is still 1).
--
Peter Gulutzan
Database Group / MySQL www.mysql.com
Sun Microsystems of Canada Inc.
Edmonton, AB, Canada
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
