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
