Re: [GENERAL] Conversion to 8.3
On Saturday 05 April 2008 11:21, Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] 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 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conversion to 8.3
Scott Marlowe [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conversion to 8.3
On Friday 04 April 2008 16:36, Tom Lane wrote: Terry Lee Tucker [EMAIL PROTECTED] 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 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conversion to 8.3
Terry Lee Tucker [EMAIL PROTECTED] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conversion to 8.3
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 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conversion to 8.3
On Friday 04 April 2008 16:36, Tom Lane wrote: Terry Lee Tucker [EMAIL PROTECTED] 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 [EMAIL PROTECTED] www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Conversion to 8.3
On Fri, Apr 4, 2008 at 2:39 PM, Terry Lee Tucker [EMAIL PROTECTED] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
