Thread: convert interval info into days

Howdy:

Running PostgreSQL 7.2.1 on RedHat Linux 7.2.

How can I convert data in a table that has been created
with the INTERVAL data type into a numeric format?

Say, I have a table with this type of data:

[snip]
-1 years   | 00:00
 00:00      | 1 year 90 days
 00:00      | 1 year 90 days
 00:00      | 1 year 90 days
 00:00      | 1 year 90 days
 00:00      | 1 year 90 days
 00:00      | 1 year 90 days
 1 year     | 2 years
 42 days    | 2 years
 42 days    | 2 years
 -1 years   | 00:00
 -1 years   | 00:00
 -1 years   | 00:00
 42 days    | 2 years
 1 year     | 2 years
[/snip]

I want to convert all of this into days, but
I don't think I can use 'to_char' because
I get things like this:

[snip example]
test_db=> select to_char('2 years 3 mons 17 days'::interval, 'YY MM DD');
 to_char
----------
 02 03 17

--

test_db=> select to_char('2 years'::interval)/365;
ERROR:  Function 'to_char(interval)' does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts
[/snip example]

Any suggestions?

Thanks!

-X

On Fri, Aug 22, 2003 at 13:00:57 -0400,
  "Johnson, Shaunn" <SJohnson6@bcbsm.com> wrote:
> Howdy:
>
> I want to convert all of this into days, but
> I don't think I can use 'to_char' because
> I get things like this:

Converting intervals to days is a problem because months are treated as
having 30 days if you try to extract the number of seconds from the
interval (which could then be converted to days). If you have a base
date the interval is relative to, then I think you can get what you
want by extract the epoch from (base_date + interval) - base_date and
then divide by 24*60*60.