List:General Discussion« Previous MessageNext Message »
From:Roy Lyseng Date:January 25 2009 3:27pm
Subject:Re: WL#946 and Changing time literal format
View as plain text  

Michael Widenius wrote:
> Hi!
> 
>>>>>> "Peter" == Peter Gulutzan <Peter.Gulutzan@stripped> 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.
> 
> 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.
> 
>>> 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.

ISO 9075 (ANSI SQL) is very strict about this. It only allows TIME 
literals with 3 or 4 digit groups, and it only allows the ':' separator 
(except after the seconds part). There is no possibility for ambiguity, 
as the first number is always interpreted as an hour field.

This is a literal format that is seen only by the SQL programmer, so 
there is no need for extensions. Date values provided by end users need 
to go through localization features, so that could be a different story.

Thanks,
Roy
Thread
Re: WL#946 and Changing time literal formatRoy Lyseng25 Jan
  • Re: WL#946 and Changing time literal formatMichael Widenius30 Jan
Re: WL#946 and Changing time literal formatPeter Gulutzan25 Jan
Re: WL#946 and Changing time literal formatMichael Widenius30 Jan
  • Re: WL#946 and Changing time literal formatKonstantin Osipov30 Jan
    • Re: WL#946 and Changing time literal formatJoerg Bruehe30 Jan
      • RE: WL#946 and Changing time literal formatJerry Schwartz30 Jan
    • Re: WL#946 and Changing time literal formatMichael Widenius3 Feb
      • Re: WL#946 and Changing time literal formatKonstantin Osipov5 Feb
        • Re: WL#946 and Changing time literal formatKonstantin Osipov5 Feb