Re: Date Duration Numbers

John Nix <maximum(at)shreve(dot)net> writes:

> I have a problem, I need to find out how many days there are between 2
> date fields and then add all the dates up.
>
> Example...
>
> name | start_date | end_date
> ------+------------+------------
> Joe1 | 2002-01-01 | 2002-01-02
> Joe2 | 2002-01-01 | 2002-01-03
> Joe3 | 2002-01-01 | 2002-01-04
> Joe4 | 2002-01-01 | 2002-01-05
> Joe5 | 2002-01-01 | 2002-01-06
>
> As you can see, the duration is:
>
> Joe1 - 1 day
> Joe2 - 2 days
> Joe3 - 3 days
> Joe4 - 4 days
> Joe5 - 5 days
>
> Now that I have the duration between the times, I need to add up all those
> numbers:
>
> 1 day
> 2 days
> 3 days
> 4 days
> + 5 days
> ----------
> 15 days
>
> I can use age() to get my interval (select age(date '2002-01-02', date
> '2002-01-01')), but I can't seem to use the field names with that, I have
> to manually enter in all the dates. I have about 900 entries in this
> table. Is there a way to do this? Thanks...
>
> John

test=> SELECT version();
version
---------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)

test=> CREATE TABLE test (name text, start_date date, end_date date);
CREATE
test=> INSERT INTO test (name, start_date, end_date) VALUES ('Joe1', '2002-01-01', '2002-01-02');
INSERT 68146522 1
test=> INSERT INTO test (name, start_date, end_date) VALUES ('Joe2', '2002-01-01', '2002-01-03');
INSERT 68146523 1
test=> insert INTO TEST (name, start_date, end_date) VALUES ('Joe3', '2002-01-01', '2002-01-04');
INSERT 68146524 1
test=> insert INTO TEST (name, start_date, end_date) VALUES ('Joe4', '2002-01-01', '2002-01-05');
INSERT 68146525 1
test=> INSERT INTO test (name, start_date, end_date) VALUES ('Joe5', '2002-01-01', '2002-01-06');
INSERT 68146526 1

test=> SELECT name, (end_date - start_date) AS days FROM test;
name | days
------+------
Joe1 | 1
Joe2 | 2
Joe3 | 3
Joe4 | 4
Joe5 | 5
(5 rows)

test=> SELECT sum(end_date - start_date) AS total_days FROM test;
total_days
------------
15
(1 row)

Is this what you are looking for?

Jason