# PostgreSQL – Casting integer to interval

## Problem:

So you have an integer value or field in a table and it represents a period of time, say minutes for example, and you want to interpret and use that value as an interval for the purposes of datetime manipulation; how do you do it?

## Solution:

All testing was carried out on PG 9.3.5.

There are 2 straight forward ways of turning that integer value into an interval:

```SELECT (10::INTEGER || ' minutes')::INTERVAL

SELECT 10::INTEGER * INTERVAL '1 minute'```

Note: the “::INTEGER” cast is only there to demonstrate that the value I’m using is an integer and PG isn’t doing something miraculous with it.

I tend to prefer the former as I think it’s clearer what the intention is. You’re stating that your value represents minutes rather than doing INTERVAL mathematics.

## Performance:

Using some very basic bench-marking however, it would appear that the latter is much faster (I have tried it in the opposite order as well):

```DO \$\$
DECLARE
start TIMESTAMP;
BEGIN
start := clock_timestamp();
FOR i IN 1..1000000 LOOP
PERFORM (10::INTEGER || ' minutes')::INTERVAL;
END LOOP;
RAISE NOTICE 'Cast time: %', clock_timestamp() - start;

start := clock_timestamp();
FOR i IN 1..1000000 LOOP
PERFORM 10::INTEGER * INTERVAL '1 minute';
END LOOP;
RAISE NOTICE 'Multiply time: %', clock_timestamp() - start;
END \$\$;```

Gives the following:

```NOTICE: Cast time: 00:00:01.982
NOTICE: Multiply time: 00:00:01.142```

Suggesting that the latter is just under twice as fast. This is likely due to having to cast to string first then to interval.

As the above is across 1 million iterations  I don’t think using either would have a massive impact on your code.