Conversion to 8.3

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

Terry Lee Tucker <terry@chosen-ones.org> writes:

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
With the new casting rules, this doesn't work.

Well, you could force it to work by casting new.ontime to text
explicitly, but this is a pretty horrid way of testing for a negative
interval anyhow. I'd be inclined to do something like
new.ontime < '0 seconds'

BTW, the IS NOT NULL test is redundant too, since the comparison
can't succeed for a null.

regards, tom lane

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?

Thanks...
--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com

On Friday 04 April 2008 16:36, Tom Lane wrote:

Terry Lee Tucker <terry@chosen-ones.org> writes:

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
With the new casting rules, this doesn't work.

Well, you could force it to work by casting new.ontime to text
explicitly, but this is a pretty horrid way of testing for a negative
interval anyhow. I'd be inclined to do something like
new.ontime < '0 seconds'

BTW, the IS NOT NULL test is redundant too, since the comparison
can't succeed for a null.

regards, tom lane

Thanks Tom. That's what I'm looking for.

--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com

On Friday 04 April 2008 16:36, Tom Lane wrote:

Terry Lee Tucker <terry@chosen-ones.org> writes:

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
With the new casting rules, this doesn't work.

Well, you could force it to work by casting new.ontime to text
explicitly, but this is a pretty horrid way of testing for a negative
interval anyhow. I'd be inclined to do something like
new.ontime < '0 seconds'

BTW, the IS NOT NULL test is redundant too, since the comparison
can't succeed for a null.

regards, tom lane

Thanks Tom. This is what I needed.
--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com

On Fri, Apr 4, 2008 at 2:39 PM, Terry Lee Tucker <terry@chosen-ones.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.

"Scott Marlowe" <scott.marlowe@gmail.com> writes:

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.

No, it's still around:

regression=# select '-1 day'::interval;
interval
----------
-1 days
(1 row)

regression=# set datestyle = postgres;
SET
regression=# select '-1 day'::interval;
interval
-------------
@ 1 day ago
(1 row)

The most bletcherous aspect of Terry's original coding is that it fails
entirely, and silently, if the DateStyle setting isn't what it's
assuming...

regards, tom lane

On Saturday 05 April 2008 11:21, Tom Lane wrote:

"Scott Marlowe" <scott.marlowe@gmail.com> writes:

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.

No, it's still around:

regression=# select '-1 day'::interval;
interval
----------
-1 days
(1 row)

regression=# set datestyle = postgres;
SET
regression=# select '-1 day'::interval;
interval
-------------
@ 1 day ago
(1 row)

The most bletcherous aspect of Terry's original coding is that it fails
entirely, and silently, if the DateStyle setting isn't what it's
assuming...

regards, tom lane

Well, I didn't realize that "ago" was only applicable to a certain date style
setting. I don't recall ever reading that anywhere but maybe I missed it. Now
I know how to do it correctly.

Thanks to all who responded...
--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com