Search Postgresql Archives

Re: Interval Question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Is is a "bare bones" copy of the function. The travel time is hard coded for 
the example.

By the way, I'm on version 7.4.

On Tuesday 11 January 2005 10:10 am, Timothy Perrigo saith:
> Can you post the code for the function you are having trouble with?
> The following psql query works as expected (returns a negative
> interval):
>
> select '3 days 4 hours 17 mins'::interval - '3 days 6 hours 17
> mins'::interval;
>   ?column?
> -----------
>   -02:00:00
>
> Inserting the difference into a table with an interval column also
> seems to work:
>
> azrael=# create temp table interval_test(dif interval);
> CREATE TABLE
> azrael=# insert into interval_test select '3 days 4 hours 17
> mins'::interval - '3 days 6 hours 17 mins'::interval;
> INSERT 13615943 1
> azrael=# select * from interval_test;
>      dif
> -----------
>   -02:00:00
> (1 row)
>
> On Jan 11, 2005, at 8:34 AM, Terry Lee Tucker wrote:
> > Greetings:
> >
> > I am working on a function which returns an interval value. The work
> > of the
> > function is to calculate the difference between the appointment
> > timestamp and
> > and the current timestamp, represented as an interval, and the the time
> > required to travel from point A to B, represented as an interval.
> >
> > Appoint time:	01/14/2004 15:30
> > Current time:  01/11/2004 10:43
> > Appt Interval:  @ 3 days 4 hours 17 mins
> >
> > Travel Time:   78 hours 17 minutes
> > Travel Interval:  @ 3 days 6 hours 17 mins
> >
> > As you can see, this truck is going to be 2 hours late. The return
> > value I'm
> > looking for is the difference between Appt. Interval and Travel
> > Interval, as
> > in: return (appt_interval - travel_interval).  This value will be
> > stored in a
> > column of type interval. I would like for late values to be shown as
> > negative. @ -2 hours.  I thought that subtracting the larger interval
> > from
> > the small would return this but it is always the absolute value. The
> > documentation states:
> >
> > interval values can be written with the following syntax:
> >
> > [@] quantity unit [quantity unit...] [direction]
> >
> >  Where: quantity is a number (possibly signed);
> >
> > Considering the above statement I believed that I could show this
> > difference
> > as a negative value but I haven't been able to figure out how to do
> > it. Can I
> > do this, and if so, how?
> >
> > Thanks...
> >
> >  Work: 1-336-372-6812
> >  Cell: 1-336-363-4719
> > email: terry@xxxxxxxx
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 8: explain analyze is your friend
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

-- 
Quote: 0
"Our laws and our institutions must necessarily be based upon and
 embody the teachings of the Redeemer of mankind. It is impossible
 that it should be otherwise; and in this sense and to this extent
 our civilization and our institutions are emphatically
 Christian... This is a religious people. This is historically
 true. From the discovery of this continent to the present hour,
 there is a single voice making this affirmation... we find
 everywhere a clear recognition of the same truth... These, and many
 other matters which might be noticed, add a volume of unofficial
 declarations to the mass of organic utterances that this is a
 Christian nation."

 -- Supreme Court Decision, 1892 (Church of the Holy Trinity V. US)

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@xxxxxxxx
CREATE OR REPLACE FUNCTION on_time (DATE, TIME, DATE, TIME, INTEGER)
    RETURNS INTERVAL AS '
DECLARE
    ship_date   ALIAS FOR $1;
    ship_time   ALIAS FOR $2;
    curr_date   ALIAS FOR $3;
    curr_time   ALIAS FOR $4;
    miles       ALIAS FOR $5;
    appt_window INTERVAL;
    trav_window INTERVAL;
    func_name   TEXT DEFAULT ''on_time'';
    onTime      INTERVAL;
BEGIN

    appt_window = (ship_date + ship_time) - (curr_date + curr_time);
    trav_window = ''78 hours 17 minutes'';

    raise notice ''appt_window: %'', appt_window;
    raise notice ''trav_window: %'', trav_window;

    RETURN (appt_window - trav_window);

END;
' LANGUAGE 'plpgsql';
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux