Thread: Conversion to 8.3
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 . . END IF; With the new casting rules, this doesn't work. How can I determine if this on-time value is "ago", that is, the shipment is late? Thanks for any help you can give... -- 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
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. 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 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 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