Re: Conversion to 8.3

On Fri, Apr 4, 2008 at 2:39 PM, Terry Lee Tucker <terry(at)chosen-ones(dot)org> wrote:
> On Friday 04 April 2008 15:01, Craig Ringer wrote:
>
> > Terry Lee Tucker wrote:
> > > Greetings:
> > >
> > > I am converting our application from 7.4.19 to 8.3.1. In the old scheme
> > > of things, I was generating an interval between two timestamps and
> > > evaluating the interval string in another set of trigger code. I was
> > > doing the following:
> > > IF new.ontime IS NOT NULL AND new.ontime ~* 'ago' THEN
> >
> > If I understand your question correctly, you're seeking to determine if
> > new.ontime is "in the past". If so, compare with current_timestamp /
> > current_date as appropriate, eg:
> >
> > IF new.ontime IS NOT NULL AND new.ontime < current_timestamp THEN
> >
> > (Note that current_timestamp and current_date are constant within a
> > transaction, so they might not be suitable if you have really long
> > running transactions).
> >
> > --
> > Craig Ringer
>
> Craig,
>
> Thanks for the response. I have failed to communicate the problem. The ontime
> value is calculated based on the arrival time versus the appointment time;
> therefore the current timestamp is not helpful. I don't have the appointment
> data in this trigger and thus I can't do a comparison there unless I go find
> the appointment data, which I could do, but was trying to prevent the
> overhead. My question is: Is there a way to look directly at the timestamp
> value and determine if it was "ago", that is, negative?

what type is new.ontime ?? timestamp or interval. I would expect it
to be an interval. But intervals are either negative or positive, not
"ago" unless that's something peculiar to 7.4 that I've long since
forgotten. So, if you generate this interval use a raise to show it
during the trigger, what does it look like in 8.3.1? I'm guessing
something like this:

-10 days -03:00:00

so then you can just compare it to something like:

if new.ontime < '0'::interval then it's negative, i.e in the past.

Maybe if you post a bit more of your data / schema etc... so we can
see what you were doing and what you're trying to do now would help,
but I'm guessing that you made the classic mistake of using string
comparison to do date math, when it is usually much better to let the
db do that kind of thing for you with proper interval / timestamp
stuff.