If block half-executing even though condition is false

["Rob Richardson" <Rob.Richardson@xxxxxxxxxxx>]

I’m getting some really weird behavior in a function I swear was working a couple of weeks ago.

 

For reasons I do not agree with, the database our main application relies on stores times without time zones.  Instead, we store each time twice, once as a timestamp containing wallclock time and the other as a timestamp containing UTC time.  When we need to calculate intervals across time zones, we have to handle the time change ourselves.  We haven’t been doing a very good job of it.  I came up with a function that handles it.  I was running final tests on it, and suddenly it wasn’t working any more.

 

The function contains this code:

 

    raise notice 'Answer should be: % but FinishTime is %', StartingTime + RequiredInterval, FinishTime;

 

    --return FinishTime;

   

    if extract(timezone from StartingTime) = StandardOffset and extract(epoch from UTCDifference) = DaylightOffset then

            raise notice 'Ambiguous time was recorded in daylight savings time.';

            FinishTime := FinishTime - interval '1 hour';

    end if;          

    --raise notice 'Charge % started current phase at % and will finish at %.',

            --          ChargeNum, StartingTime, FinishTime;

    return FinishTime;

 

The starting time is 3/13/11 00:00, two hours before the change from standard to daylight savings time, and the required interval is 48 hours.  The answer should be 3/15/11 01:00-04.  The if block should be executed only if the wallclock time is in the interval of 1-2 AM on the day of the fall time change, when that hour gets repeated.

 

The first raise notice statement tells me that FinishTime is 1 AM on the 15^th, as expected.  But the returned value is midnight.  If I comment out the line in the if block that takes an hour off the FinishTime, the returned value is 1 AM as expected.  But the raise notice message does not appear in my Messages window! 

 

I would understand this if the if block was just a single statement, such as would be true in C++ if I omitted the braces surrounding the block.  But in PG/PSQL, the if block is delimited by the “end if;” line.  So what is going on with this function? 

 

If you need more information (such as the entire function), please ask.

 

RobR