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.

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s