List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 2 1999 6:26pm
Subject:Re: Dates querying Mysql
View as plain text  
>>>>> "Vivek" == Vivek Khera <khera@stripped> writes:

>>>>> "MW" == Michael Widenius <monty@stripped> writes:
MW> What may confuse someone is that the simple operators: >=, >, = , <
MW> and <= does some 'extra magic' when one compares a 'date/datetime/time
MW> type' column to a constant strings to make date handling easier.  We
MW> didn't add this behaviour to the BETWEEN clause as it's harder to make
MW> a simple rule when one have 3 arguments to take care compared to two
MW> arguments.

Vivek> So, when one wants to do a "between" with dates, which is the
Vivek> preferred form?  Below, date_value is a column of type DATE.

Vivek> a)
Vivek>   where date_value BETWEEN '1999-05-01' AND '1999-05-31'

Vivek> b)
Vivek>   where date_value <= '1999-05-31' AND date_value >= '1999-05-01'

Vivek> Which one does the faster compares?

a) (because the first argument is only evaluated once)

Vivek> Why not just have the parser
Vivek> internally rewrite form (a) as (b) then apply the necessary rules for
Vivek> optimizing date/time compares?

The problem is that according to the ODBC (and probably ANSI SQL)
rules, it's the first argument in BETWEEN that decides how the
comparison is done. (ODBC/ANSI SQL only have to handle the automatic
translation between float/integer;  MySQL has the additional 'burden' of
automatic string/date/number translations, but it follows the same
rules in this case)

The problem is not implementing it;  The problem is to not have too 
many conflicts with the SQL standard.

Regards,
Monty
Thread
Dates querying MysqlClaudia M. Castaneda24 May
  • Re: Dates querying MysqlGraeme B. Davis25 May
  • Re: Dates querying MysqlChristian Mack25 May
    • Re: Dates querying MysqlClaudia M. Castaneda25 May
      • Re: Dates querying MysqlJim Faucette25 May
        • Re: Dates querying MysqlClaudia M. Castaneda25 May
          • Re: Dates querying MysqlVivek Khera25 May
        • Re: Dates querying MysqlJim Faucette25 May
          • Re: Dates querying MysqlMichael Widenius2 Jun
            • Re: Dates querying MysqlVivek Khera2 Jun
              • Re: Dates querying MysqlMichael Widenius3 Jun
Re: Dates querying MysqlVivek Khera2 Jun