Postgresql, add (years, months or days) to date based on another column

Question

I need a query from a 8.4 POSTGRESQL database to list all my products ordered by a date that represents the actual day that the warranty of a product ends.

The table is something like this, I'll make it simple:

PRODUCT (varchar) 
WARRANTY (int)
TYPE_WARRANTY (char) ( 'Y', 'M', 'D' ) -- Years, Months or Days
CREATED_AT (date)

To give you an example:

PRODUCT  |  WARRANTY  |  TYPE_WARRANTY  |  CREATED_AT 
------------------------------------------------------
'PROD A' |      1     |       'Y'       | '2014-01-01'
'PROD B' |     10     |       'M'       | '2014-06-01'
'PROD C' |     30     |       'D'       | '2014-01-01'

What I need is a query that will bring me this:

PRODUCT  |  WARRANTY  |  TYPE_WARRANTY  |  CREATED_AT  | WARRANTY_ENDS | DAYS
-----------------------------------------------------------------------------
'PROD C' |    30      |       'D'       | '2014-01-01' | '2014-01-31'  | -175
'PROD A' |     1      |       'Y'       | '2014-01-01' | '2015-01-01'  | 160
'PROD B' |    10      |       'M'       | '2014-06-01' | '2015-04-01'  | 250

I hope you can understand it

So what I need is select all products and order them by when the warranty ends including how many days between today and that date.

What I already have:

I know how to get the days, I know how to add the interval to the date, all I need now is a way to make the query understands that I want to add the interval based on the value of the type_warranty column.

I used '1 + year' as an example here and it works but I need it to work dynamically like '1 + month' and '1 + day' too.

SELECT 
  PRODUCT, WARRANTY, TYPE_WARRANTY, CREATED_AT, 
  (CREATED_AT + (WARRANTY * '1 year'::INTERVAL)) as WARRANTY_ENDS,
  EXTRACT(day from age((CREATED_AT + '1 year', current_date)) as days
FROM TABLE
order by days; 

This works, but only for years of course, I have no idea on how to transform that '1 year' into '1 month' or '1 day' depending on the warranty_type

Do you have any idea to help?

Answer 1

Avoid the clumsiness of a case everytime you need to do date arithmetic using a foreign table. In this example it is a CTE table but it should be a real one. Or just change the WARRANTY column to interval.

with itval (type_warranty, itvalue) as (
    values ('D', '1 day'::interval), ('M', '1 month'), ('Y', '1 year')
)
select 
    product,
    warranty,
    type_warranty,
    created_at, 
    created_at + (warranty * itvalue) as warranty_ends,
    (created_at + (warranty * itvalue))::date - current_date as days
from
    t
    inner join
    itval using (type_warranty)
order by days;

Your days arithmetic based on extract and age does not work.

Answer 2

I don't have PostgreSQL 8.4 installed right now but does this work?

SELECT
    PRODUCT, WARRANTY, TYPE_WARRANTY, CREATED_AT, 
    (CREATED_AT + (WARRANTY *
        CASE TYPE_WARRANTY
            WHEN 'Y' THEN '1 year'::interval
            WHEN 'M' THEN '1 month'::interval
            WHEN 'D' THEN '1 day'::interval
        END
    ))::date as WARRANTY_ENDS,
    (created_at + (warranty * itvalue))::date - current_date as days
    FROM TABLE;