Re: Date Duration Numbers

John Nix <maximum@shreve.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