CODEWITHSUNDEEP
postgresqlplpgsqlintervals
user1086498
user1086498

Reputation:

postgreSQL - return interval as time type

In a form we have, people will enter an age by first specifying a category (minutes, hours, days, months, years) and then secondarily entering an integer value. The problem with this practice is that ages become inconsistent - 18 months is greater than 1 year, this makes statistical analysis difficult.

Internally I need to store this data not as two integers (as it is currently stored) but as an interval and an integer. The second integer indicates the original unit.

Here is the question: what is the simple method for telling postgreSQL to return this interval back as the time it was entered as? So for instance, the user enters: 18 months. Right now, it will return it as 1 year, 6 months. -- can't redisplay that on the form. I know based on the other integer what they entered it as, so there will be no fractional component - i.e. if they entered it in days, there will be no hours, etc.

Ideally it would be something like

SELECT as_time((person.age).value,'months')

Note that these intervals are entered as-is, so the months would not ever be anything other than the 30-day interval 'months' (representational months). If need be I can use a plpgsql function for it, since I'll need to do a lookup on the time type code; but the strict requirement in all cases is that if they entered some time, they get back exactly that time, no rounding, no 'correction', etc.

Upvotes: 0

Views: 931

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658927

WITH x(unit, t) AS (
    VALUES
     (1, interval '1 month')
    ,(1, interval '2 month')
    ,(1, interval '3 month')
    ,(1, interval '12 month')
    ,(1, interval '13 month')
    ,(1, interval '18 month')
    ,(1, interval '24 month')
    ,(1, interval '30 month')
    ,(1, interval '300 month')
    ,(2, interval '1 year')
    ,(2, interval '2 year')
    ,(2, interval '5 year')
    ,(2, interval '100 year')
    )
SELECT CASE unit
         WHEN 1 THEN EXTRACT(year FROM t)::int * 12
                 + EXTRACT(month FROM t)::int 
         WHEN 2 THEN EXTRACT(year FROM t)::int
         ELSE -1 -- should not occour
         END AS units
      ,CASE unit
         WHEN 1 THEN 'months'
         WHEN 2 THEN 'years'
         ELSE 'unknown' -- should not occour
       END as unit
FROM   x;

EXTRACT() in the manual.

You could try and extract the epoch value (count of seconds) and convert it - like @Magnus demonstrates here, but months and years are justified for the irregular nature of months, so the calculation would be off with bigger intervals.

Upvotes: 1

Related Questions