Thread: Interval Question
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@esc1.com
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@esc1.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
On Tue, 11 Jan 2005, Terry Lee Tucker wrote: > 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? Well, when I try this with constant values on my 7.4 or 8.0 dev system I get: select '3 days 4 hours 17 mins'::interval - '3 days 6 hours 17 mins'::interval; ?column? ----------- -02:00:00 (1 row) Can you give a full example?
Terry Lee Tucker <terry@esc1.com> writes:
> I thought that subtracting the larger interval from
> the small would return this but it is always the absolute value.
Eh?
regression=# set DateStyle TO ISO;
SET
regression=# select '@ 3 days 4 hours 17 mins'::interval -
regression-# '@ 3 days 6 hours 17 mins'::interval;
?column?
-----------
-02:00:00
(1 row)
regression=# set DateStyle TO postgres;
SET
regression=# select '@ 3 days 4 hours 17 mins'::interval -
regression-# '@ 3 days 6 hours 17 mins'::interval;
?column?
---------------
@ 2 hours ago
(1 row)
and as for detecting whether it's negative,
regression=# select ('@ 3 days 4 hours 17 mins'::interval -
regression(# '@ 3 days 6 hours 17 mins'::interval) < '0'::interval;
?column?
----------
t
(1 row)
regards, tom lane
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@esc1.com > > > > ---------------------------(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@esc1.com
Attachment
Check this out:
rnd=# show DateStyle;
DateStyle
-----------
SQL, MDY
(1 row)
rnd=# select interval '@ 3 days 4 hours 17 mins'::interval - '@ 3 days 6 hours
17 mins'::interval;
?column?
-----------
@ 2 hours
(1 row)
And...
rnd=# set DateStyle to postgres;
SET
rnd=# select interval '@ 3 days 4 hours 17 mins'::interval - '@ 3 days 6 hours
17 mins'::interval;
?column?
---------------
@ 2 hours ago
(1 row)
Here is my Version:
PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-42)
On Tuesday 11 January 2005 10:19 am, Tom Lane saith:
> Terry Lee Tucker <terry@esc1.com> writes:
> > I thought that subtracting the larger interval from
> > the small would return this but it is always the absolute value.
>
> Eh?
>
> regression=# set DateStyle TO ISO;
> SET
> regression=# select '@ 3 days 4 hours 17 mins'::interval -
> regression-# '@ 3 days 6 hours 17 mins'::interval;
> ?column?
> -----------
> -02:00:00
> (1 row)
>
> regression=# set DateStyle TO postgres;
> SET
> regression=# select '@ 3 days 4 hours 17 mins'::interval -
> regression-# '@ 3 days 6 hours 17 mins'::interval;
> ?column?
> ---------------
> @ 2 hours ago
> (1 row)
>
> and as for detecting whether it's negative,
>
> regression=# select ('@ 3 days 4 hours 17 mins'::interval -
> regression(# '@ 3 days 6 hours 17 mins'::interval) < '0'::interval;
> ?column?
> ----------
> t
> (1 row)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
--
Quote: 59
"The concessions of the weak are the concessions of fear."
--Mark Twain
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com
Apparently, if DateStyle is set to Sql, it always returns the absolute value.
Is this due to some Sql standard or is it a bug?
On Tuesday 11 January 2005 10:19 am, Tom Lane saith:
> Terry Lee Tucker <terry@esc1.com> writes:
> > I thought that subtracting the larger interval from
> > the small would return this but it is always the absolute value.
>
> Eh?
>
> regression=# set DateStyle TO ISO;
> SET
> regression=# select '@ 3 days 4 hours 17 mins'::interval -
> regression-# '@ 3 days 6 hours 17 mins'::interval;
> ?column?
> -----------
> -02:00:00
> (1 row)
>
> regression=# set DateStyle TO postgres;
> SET
> regression=# select '@ 3 days 4 hours 17 mins'::interval -
> regression-# '@ 3 days 6 hours 17 mins'::interval;
> ?column?
> ---------------
> @ 2 hours ago
> (1 row)
>
> and as for detecting whether it's negative,
>
> regression=# select ('@ 3 days 4 hours 17 mins'::interval -
> regression(# '@ 3 days 6 hours 17 mins'::interval) < '0'::interval;
> ?column?
> ----------
> t
> (1 row)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
--
Quote: 88
"The best view of big government is in the rearview mirror as you're
driving away from it."
--Ronald Reagan
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com
Thanks for the answers.
By the way, I'm not trying to parse the textual output to discover if it is
netative. Apparently, I failed to communicate my purpose properly. I just
want to return the value, regardless of netative or positive, to the user and
store it in a column of type interval. I simply wanted it to show up as a
netative value if the load is going to be late.
Thanks for all the help...
On Tuesday 11 January 2005 01:19 pm, Tom Lane saith:
> Terry Lee Tucker <terry@esc1.com> writes:
> > Apparently, if DateStyle is set to Sql, it always returns the absolute
> > value. Is this due to some Sql standard or is it a bug?
>
> It's a bug in interval_out. Looks like it gets it wrong for GERMAN
> style too. Surprising no one noticed before.
>
> (In any case, I dunno why you are parsing the textual output to discover
> whether an interval is negative...)
>
> regards, tom lane
>
> Soon-to-be-applied patch:
>
> *** src/backend/utils/adt/datetime.c.orig Fri Dec 31 17:46:13 2004
> --- src/backend/utils/adt/datetime.c Tue Jan 11 13:13:30 2005
> ***************
> *** 3932,3938 ****
> cp += strlen(cp);
> }
>
> ! if (is_before && (style == USE_POSTGRES_DATES))
> {
> strcat(cp, " ago");
> cp += strlen(cp);
> --- 3932,3938 ----
> cp += strlen(cp);
> }
>
> ! if (is_before && (style != USE_ISO_DATES))
> {
> strcat(cp, " ago");
> cp += strlen(cp);
--
Quote: 83
"Government is not the solution to our problem. Government is the
problem."
--Ronald Reagan
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com
Terry Lee Tucker <terry@esc1.com> writes:
> Apparently, if DateStyle is set to Sql, it always returns the absolute value.
> Is this due to some Sql standard or is it a bug?
It's a bug in interval_out. Looks like it gets it wrong for GERMAN
style too. Surprising no one noticed before.
(In any case, I dunno why you are parsing the textual output to discover
whether an interval is negative...)
regards, tom lane
Soon-to-be-applied patch:
*** src/backend/utils/adt/datetime.c.orig Fri Dec 31 17:46:13 2004
--- src/backend/utils/adt/datetime.c Tue Jan 11 13:13:30 2005
***************
*** 3932,3938 ****
cp += strlen(cp);
}
! if (is_before && (style == USE_POSTGRES_DATES))
{
strcat(cp, " ago");
cp += strlen(cp);
--- 3932,3938 ----
cp += strlen(cp);
}
! if (is_before && (style != USE_ISO_DATES))
{
strcat(cp, " ago");
cp += strlen(cp);
Tom Lane wrote: > Terry Lee Tucker <terry@esc1.com> writes: > >>Apparently, if DateStyle is set to Sql, it always returns the absolute value. >>Is this due to some Sql standard or is it a bug? > > > It's a bug in interval_out. Looks like it gets it wrong for GERMAN > style too. Surprising no one noticed before. Any idea when I might be able to put my hands on Red Hat 3 rpm versions that include this fix? Currently running postgresql-server-7.4.6-2PGDG and would like to stick with the same version. It'd be a whole lot easier to apply the fix via rpm rather then having to build from source. -- Until later, Geoffrey
Geoffrey <esoteric@3times25.net> writes:
> Tom Lane wrote:
>> It's a bug in interval_out. Looks like it gets it wrong for GERMAN
>> style too. Surprising no one noticed before.
> Any idea when I might be able to put my hands on Red Hat 3 rpm versions
> that include this fix?
[ shrug... ] Whenever there is a 7.4.7 release. This bug isn't going
to excite anyone into pushing such a release out soon.
regards, tom lane
On Tue, 11 Jan 2005, Geoffrey wrote:
> Tom Lane wrote:
>> Terry Lee Tucker <terry@esc1.com> writes:
>>
>>> Apparently, if DateStyle is set to Sql, it always returns the absolute
>>> value.
>>> Is this due to some Sql standard or is it a bug?
>>
>>
>> It's a bug in interval_out. Looks like it gets it wrong for GERMAN
>> style too. Surprising no one noticed before.
>
> Any idea when I might be able to put my hands on Red Hat 3 rpm versions that
> include this fix?
>
> Currently running postgresql-server-7.4.6-2PGDG
> and would like to stick with the same version. It'd be a whole lot easier to
> apply the fix via rpm rather then having to build from source.
If you really need it, you can rebuild the rpm itself from the .src.rpm.
Adding a small patch is quite easy, even if you're not familiar
with spec files. Usually there are other patches already, all you need
is to move the patch file to the right place (SOURCES in the rpm tree)
and add one PatchXXX: and one %patchXXX line. Then use rpmbuild
(note that you need the development set of packages installed, of course).
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
We, that is, Geoffrey, applied the patch and rebuilt the rpm's. I have installed the new rpm and the problem is resolved. We are in the process of converting a transportation package from a commercial database product to Postgres. On at least two occassions, I have reported bugs to that software company, and the bugs were never fixed. We were given work-around hacks and that was it. We discovered this problem yesterday and it is fixed today. I guess that is one of many GREAT things about Open Source software ;o) Thanks to everyone who responded, and especially to Tom Lane, who provided the patch. On Wednesday 12 January 2005 04:32 am, Marco Colombo saith: > On Tue, 11 Jan 2005, Geoffrey wrote: > > Tom Lane wrote: > >> Terry Lee Tucker <terry@esc1.com> writes: > >>> Apparently, if DateStyle is set to Sql, it always returns the absolute > >>> value. > >>> Is this due to some Sql standard or is it a bug? > >> > >> It's a bug in interval_out. Looks like it gets it wrong for GERMAN > >> style too. Surprising no one noticed before. > > > > Any idea when I might be able to put my hands on Red Hat 3 rpm versions > > that include this fix? > > > > Currently running postgresql-server-7.4.6-2PGDG > > and would like to stick with the same version. It'd be a whole lot > > easier to apply the fix via rpm rather then having to build from source. > > If you really need it, you can rebuild the rpm itself from the .src.rpm. > Adding a small patch is quite easy, even if you're not familiar > with spec files. Usually there are other patches already, all you need > is to move the patch file to the right place (SOURCES in the rpm tree) > and add one PatchXXX: and one %patchXXX line. Then use rpmbuild > (note that you need the development set of packages installed, of course). > > .TM. > -- > ____/ ____/ / > / / / Marco Colombo > ___/ ___ / / Technical Manager > / / / ESI s.r.l. > _____/ _____/ _/ Colombo@ESI.it > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match Work: 1-336-372-6812 Cell: 1-336-363-4719 email: terry@esc1.com