How to declare a variable INTERVAL in postgresql?

Question

I want to write a code that interates from TIMESTAMP_START to TIMESTAMP_END with an variable INTERVAL. My hardcoded working code looks like this:

LOOP 
    ...DO SMTH...

    TIMESTAMP_START := (TIMESTAMP_START + INTERVAL '30' MINUTE);
    EXIT WHEN TIMESTAMP_START > TIMESTAMP_END;
END LOOP;

When I try to replace the type of the interval I get a Syntax Error:

DECLARE INCREMENT_TYPE  INTERVAL := minute;
...
TIMESTAMP_START := (TIMESTAMP_START + INTERVAL '30' INCREMENT_TYPE);

The same happens whe I try to variable the step:

DECLARE STEP    text    := '30'; 
...
TIMESTAMP_START := (TIMESTAMP_START + INTERVAL STEP MINUTE);

I assuming I am doing something wrong, but googling the documentations does not gave me the answer. What is the correct procedure to this problem?

user330315 · Accepted Answer · 2018-12-07 12:40:25Z

A variable can only hold a complete value of the underlying data type. You couldn't store just 2018 in a timestamp variable either.

Depending on how you want to use it, and how flexible it should be I see following possibilities:

declare 
   increment_value  interval := interval '30' minute;

begin
...
   timestamp_start := (timestamp_start + increment_value);
...

That would be helpful if you want to define the increment value only once.

If you want to use different interval lengths throughout the function, you could define an interval of a standard length and then multiply that:

declare 
   one_minute_interval interval := interval '1' minute;
   one_hour_interval interval := interval '1' hour;

begin
...
   timestamp_start := (timestamp_start + 30 * one_minute_interval);
...
   timestamp_start := (timestamp_start + 5 * one_hour_interval);

Or a solution that is closer to what you are trying to do:

declare 
   increment_type  text := 'minute';

begin
...
   timestamp_start := (timestamp_start + ('30 '||increment_type)::interval);
...

Collectives™ on Stack Overflow

How to declare a variable INTERVAL in postgresql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related