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?
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.
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.