List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:July 20 2004 6:57am
Subject:Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48am
View as plain text  
Gerard Gilliland wrote:

> Michael:
>   Again, Thank you for your excellent suggestions and quick response !!
>   I agree that "Two types of data means you should use two columns."
>   There is no excuse for bad design.
>   (I inherited the database and moved it to MySQL -- I should have converted
> then.)
>   I will split the Source table into N and Period (probably with better
> names.)
>   (I am indeed working with a "temp" table.
>   Only for debug purposes and to test before I sent the original problem.)
> 
>   Concerning:
> 
>>>$qry = "SELECT PlantName, Unit, TagName, CalDate, nPeriod,
>>>        TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) AS n,
>>>        TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1)) AS Period,
>>>        CASE
>>>          WHEN Period = 'Month' THEN DATE_ADD(CalDate, INTERVAL n MONTH)
>>>          WHEN Period = 'Year' THEN DATE_ADD(CalDate, INTERVAL n YEAR)
>>>        END
>>>        AS DueDate
>>>        FROM tblTemp";
> 
>   Did you try it?  (the above)
>   -- Yes
>   It should work.
>    -- I agree.  However, It fails.
>   It doesn't work with "Temporary Calculated" columns
<snip>
> I think it fails for the same reason, I have trouble with using criteria
> against "DueDate"
<snip>
> 
> Fails (in the WHERE clause):
>  $qry = "SELECT PlantName, Unit, TagName, CalDate, CalPeriod, CalPeriodType,
>          CASE
>            WHEN CalPeriodType = 'Month' THEN DATE_ADD(CalDate, INTERVAL
> CalPeriod MONTH)
>             WHEN CalPeriodType = 'Year' THEN DATE_ADD(CalDate, INTERVAL
> CalPeriod YEAR)
>           END
>         AS DueDate
>         FROM tblTemp
>         WHERE DueDate  > '2005-01-01'";
> (Note: DueDate is a "TemporaryCalculated" field)

Ahh, that's different.  You didn't mention trying to use DueDate in the 
WHERE clause before.  You can't do that.  The purpose of the WHERE clause is 
to indicate which rows should be used for your calculations, but you are 
asking mysql to choose rows based on the result of the calculations.  You 
see the problem?  You are asking mysql to pick the rows to operate on based 
on the result of the operation.  In other words, DueDate doesn't exist yet 
when the WHERE clause is evaluated, so you can't use it there.

Fortunately, there's a solution.  The HAVING clause filters rows near the 
end, after the calculations have been done, so it is the place to use 
DueDate to limit your results.  Thus, this should work:

   SELECT PlantName, Unit, TagName, CalDate, CalPeriod, CalPeriodType,
     CASE
       WHEN CalPeriodType = 'Month'
         THEN DATE_ADD(CalDate, INTERVAL CalPeriod MONTH)
       WHEN CalPeriodType = 'Year'
         THEN DATE_ADD(CalDate, INTERVAL CalPeriod YEAR)
     END
     AS DueDate
   FROM tblTemp
   HAVING DueDate  > '2005-01-01';

> Works:
> $qry = "SELECT PlantName, Unit, TagName, CalDate, CalPeriod, CalPeriodType,
>           CASE
>             WHEN CalPeriodType = 'Month' THEN DATE_ADD(CalDate, INTERVAL
> CalPeriod MONTH)
>             WHEN CalPeriodType = 'Year' THEN DATE_ADD(CalDate, INTERVAL
> CalPeriod YEAR)
>           END
>         AS DueDate
>         FROM tblTemp
>         WHERE
>           CASE
>             WHEN CalPeriodType = 'Month' THEN DATE_ADD(CalDate, INTERVAL
> CalPeriod MONTH)
>             WHEN CalPeriodType = 'Year' THEN DATE_ADD(CalDate, INTERVAL
> CalPeriod YEAR)
>           END
>         > '2005-01-01'";
> (Note: Where Criteria does not contain any "TemporaryCalculated" field.)

Well, it does, sort of.  Here you force the calculation in the WHERE clause 
by explicitly doing the calculations.  This will work, but it will be less 
efficient unless the optimizer is smart enough not to do the calculations twice.

> A simpler case of the above (but continuing the "TemporaryCalculation" field
> discussion.)
> 
> Fails:
>      $qry = "SELECT PlantName, Unit, TagName, CalDate, CalPeriod,
> CalPeriodType,
>                (CalPeriod + Unit) AS CalNum
>                FROM tblTemp
>               WHERE CalNum > 7";
> 
> Works:
>      $qry = "SELECT PlantName, Unit, TagName, CalDate, CalPeriod,
> CalPeriodType,
>                (CalPeriod + Unit) AS CalNum
>                FROM tblTemp
>               WHERE (CalPeriod + Unit) > 7";

Right.  This is the same as above.  The second works by forcing the 
calculation in the WHERE clause.  The first would work if you changed WHERE 
to HAVING.

> I do appreciate the thoroughness of your effort in coversion to Months,
> However the potential for using Days looms ahead, and the complexity of
> Days in combinations with Month lengths, and Leap years
> causes more confusion than the well defined CASE of Day, Month, Year.

Agreed.

I should also point out that because we are selecting rows based on DueDate, 
and DueDate is a calculated value, there is no index to help us out.  In 
other words, even if there is an index on CalDate, we render it useless (for 
the purposes of this query) when we feed CalDate into a function to get 
DueDate.  The result is that we are asking for an inefficient full-table 
scan.  Mysql must calculate DueDate for *every* row, then compare the 
results (in HAVING) to decide which results to show us.  Depending on the 
size of your table and how often this query is run, that may or may not be a 
problem to you.

It may be advisable to replace the Period and PeriodType columns with a 
DueDate column.  Something like:

   ALTER TABLE tblTemp ADD COLUMN DueDate DATE;

   UPDATE tblTemp
   SET DueDate= CASE
                  WHEN CalPeriodType = 'Month'
                    THEN DATE_ADD(CalDate, INTERVAL CalPeriod MONTH)
                  WHEN CalPeriodType = 'Year'
                    THEN DATE_ADD(CalDate, INTERVAL CalPeriod YEAR)
                END;

   ALTER TABLE tblTemp DROP COLUMN CalPeriod, DROP COLUMN CalPeriodType;

Then you would do the same calculations when inserting new data.  In this 
case, you can use DueDate in the WHERE clause:

   SELECT PlantName, Unit, TagName, CalDate, DueDate
   FROM tblTemp
   WHERE DueDate > '2005-01-01';

and an index on DueDate could be used to make finding matching rows very fast.

> Again -- Thank you for your excellent support and quick response.
> 
> Gerard Gilliland

Michael

Thread
Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48amGerard Gilliland17 Jul
  • Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48amMichael Stassen19 Jul
  • Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48amGerard Gilliland20 Jul
    • Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48amMichael Stassen20 Jul