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 15th, 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
