Обсуждение: adding times togeather
i have two collumns of type time, and i want to get the result of the two times added togeather. how do i go about doing this? i could use timestamps instead of times if necessary, but all the date/time functions seem to want to add intervals to things. thanks, ed
I can't tell what you want. Adding two times doesn't make sense. What is 3:15pm + 4:30am or 5:15 April 3 2003 + 4:27 June 14 1997. No such beast exists. I suspect that one of your "times" is really an interval. You can: Subtract two times and get an interval Add an interval to a time to get a time Add two intervals to get an interval What are you trying to do? It might help to tell us what the two columns represent and what the result is supposed to represent. Cheers, Steve On Monday 28 April 2003 3:17 pm, ed despard wrote: > i have two collumns of type time, and i want to get the result of the > two times added togeather. how do i go about doing this? i could use > timestamps instead of times if necessary, but all the date/time > functions seem to want to add intervals to things. > > thanks, > ed > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
what i have is two columns that represent the times for sections of a race, so i have column A that is say 0:20:23 and i have column B that is 0:21:45 and i want to have a column that is A+B where that would be 0:42:18 in this case. ed On Monday, April 28, 2003, at 06:25 PM, Steve Crawford wrote: > I can't tell what you want. Adding two times doesn't make sense. What > is > 3:15pm + 4:30am or 5:15 April 3 2003 + 4:27 June 14 1997. No such beast > exists. I suspect that one of your "times" is really an interval. > > You can: > Subtract two times and get an interval > Add an interval to a time to get a time > Add two intervals to get an interval > > What are you trying to do? It might help to tell us what the two > columns > represent and what the result is supposed to represent. > > Cheers, > Steve > > > On Monday 28 April 2003 3:17 pm, ed despard wrote: >> i have two collumns of type time, and i want to get the result of the >> two times added togeather. how do i go about doing this? i could use >> timestamps instead of times if necessary, but all the date/time >> functions seem to want to add intervals to things. >> >> thanks, >> ed >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 4: Don't 'kill -9' the postmaster
On Mon, 28 Apr 2003, ed despard wrote:
> what i have is two columns that represent the times for sections of a
> race, so i have column A that is say 0:20:23 and i have column B that
> is 0:21:45 and i want to have a column that is A+B where that would be
> 0:42:18 in this case.
Store them as intervals and you can do that pretty easily:
create table th (n interval, p interval);
CREATE TABLE
marl8412=# insert into th values ('00:02:23','00:03:43');
INSERT 4505763 1
marl8412=# select n+p as time from th;
time
----------
00:06:06
On Mon, 28 Apr 2003, Steve Crawford wrote:
> I can't tell what you want. Adding two times doesn't make sense. What is
> 3:15pm + 4:30am or 5:15 April 3 2003 + 4:27 June 14 1997. No such beast
> exists. I suspect that one of your "times" is really an interval.
>
> You can:
> Subtract two times and get an interval
> Add an interval to a time to get a time
> Add two intervals to get an interval
This obviously tells you how to achieve what you want, ie.
select time1 + (time2 - '0 seconds'::interval)
but there's still the meaning issue.
>
> What are you trying to do? It might help to tell us what the two columns
> represent and what the result is supposed to represent.
>
> Cheers,
> Steve
>
>
> On Monday 28 April 2003 3:17 pm, ed despard wrote:
> > i have two collumns of type time, and i want to get the result of the
> > two times added togeather. how do i go about doing this? i could use
> > timestamps instead of times if necessary, but all the date/time
> > functions seem to want to add intervals to things.
> >
> > thanks,
> > ed
--
Nigel J. Andrews
Ah-ha: you actually have intervals, not times. Make the columns interval data type. Cheers, Steve On Monday 28 April 2003 3:36 pm, ed despard wrote: > what i have is two columns that represent the times for sections of a > race, so i have column A that is say 0:20:23 and i have column B that > is 0:21:45 and i want to have a column that is A+B where that would be > 0:42:18 in this case. > > ed > > On Monday, April 28, 2003, at 06:25 PM, Steve Crawford wrote: > > I can't tell what you want. Adding two times doesn't make sense. What > > is > > 3:15pm + 4:30am or 5:15 April 3 2003 + 4:27 June 14 1997. No such beast > > exists. I suspect that one of your "times" is really an interval. > > > > You can: > > Subtract two times and get an interval > > Add an interval to a time to get a time > > Add two intervals to get an interval > > > > What are you trying to do? It might help to tell us what the two > > columns > > represent and what the result is supposed to represent. > > > > Cheers, > > Steve > > > > On Monday 28 April 2003 3:17 pm, ed despard wrote: > >> i have two collumns of type time, and i want to get the result of the > >> two times added togeather. how do i go about doing this? i could use > >> timestamps instead of times if necessary, but all the date/time > >> functions seem to want to add intervals to things. > >> > >> thanks, > >> ed > >> > >> > >> ---------------------------(end of > >> broadcast)--------------------------- > >> TIP 4: Don't 'kill -9' the postmaster > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
On Mon, Apr 28, 2003 at 02:25:54PM -0800, Steve Crawford wrote:
> You can:
> Subtract two times and get an interval
> Add an interval to a time to get a time
Beware: you cannot (in 7.2.3 at least -- I don't have 7.3 available for
testing) add a timestamp to an interval and expect a reasonable value.
You have to add the interval to the timestamp.
h=> select '05:04:03'::interval + '2003-mar-01 09:05:00'::timestamp;
?column?
---------------------
2003-03-01 05:04:03
(1 row)
h=> select '2003-mar-01 09:05:00'::timestamp + '05:04:03'::interval;
?column?
------------------------
2003-03-01 14:09:03-03
(1 row)
h=> select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 7.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)
On Tuesday 29 Apr 2003 12:26 am, Alvaro Herrera Munoz wrote: > On Mon, Apr 28, 2003 at 02:25:54PM -0800, Steve Crawford wrote: > > You can: > > Subtract two times and get an interval > > Add an interval to a time to get a time > > Beware: you cannot (in 7.2.3 at least -- I don't have 7.3 available for > testing) add a timestamp to an interval and expect a reasonable value. > You have to add the interval to the timestamp. > > h=> select '05:04:03'::interval + '2003-mar-01 09:05:00'::timestamp; > ?column? > --------------------- > 2003-03-01 05:04:03 > (1 row) In 7.3.2 it just doesn't allow it (undefined operator). Irritating, but better than the above oddity. -- Richard Huxton