declare a variable inside a function

Efi · September 2023

Hi!
I need to cast input value into string and than use it within the interval option.
Can I declare a variable inside a function?
The use is for: "current_timestamp + interval to_char(input) minute"
the input is numeric.

Bryan_H · September 2023

You can CAST a string to an interval, e.g. SELECT CAST('30 sec' AS INTERVAL);
Try concatenating your string like this: SELECT current_timestamp + CAST(input||' minute' AS INTEVRAL);
Please see also: https://docs.vertica.com/12.0.x/en/sql-reference/data-types/datetime-data-types/interval/casting-with-intervals/

marcothesane · September 2023

Why would you need to do that?

I know three - all pretty intuitive - ways to add minutes to timestamps.

knowing that a minute is 1/1440 of a day, add n times 1/1440 to an input timestamp
use the de-facto standard TIMESTAMPADD(<_date_part_>,<_counter_>,<_datetime_val_>) function
cast the integer to VARCHAR(2), concatenate '00:' in front of it; cast the resulting string of, for example , '00:3', as an INTERVAL (by putting a pair of round parentheses around the concatenation operation - so it does not try to cast just the integer cast to VARCHAR, to INTERVAL)

WITH
mi(mi) AS (
            SELECT 1
  UNION ALL SELECT 2
  UNION ALL SELECT 3
  UNION ALL SELECT 4
  UNION ALL SELECT 5
  UNION ALL SELECT 6
)
SELECT
  CURRENT_TIMESTAMP                                     AS current_ts
, CURRENT_TIMESTAMP + (mi / 1440)                       AS just_add_1_over_1440
, TIMESTAMPADD('mi',mi,CURRENT_TIMESTAMP)               AS use_timestampadd
, CURRENT_TIMESTAMP + ('00:'||mi::varchar(2))::INTERVAL AS cast_to_string_and_add_interval
FROM mi;
          current_ts           |     just_add_1_over_1440      |       use_timestampadd        | cast_to_string_and_add_interval 
-------------------------------+-------------------------------+-------------------------------+---------------------------------
 2023-09-14 12:20:38.422197+02 | 2023-09-14 12:21:38.422197+02 | 2023-09-14 12:21:38.422197+02 | 2023-09-14 12:21:38.422197+02
 2023-09-14 12:20:38.422197+02 | 2023-09-14 12:22:38.422197+02 | 2023-09-14 12:22:38.422197+02 | 2023-09-14 12:22:38.422197+02
 2023-09-14 12:20:38.422197+02 | 2023-09-14 12:23:38.422197+02 | 2023-09-14 12:23:38.422197+02 | 2023-09-14 12:23:38.422197+02
 2023-09-14 12:20:38.422197+02 | 2023-09-14 12:24:38.422197+02 | 2023-09-14 12:24:38.422197+02 | 2023-09-14 12:24:38.422197+02
 2023-09-14 12:20:38.422197+02 | 2023-09-14 12:25:38.422197+02 | 2023-09-14 12:25:38.422197+02 | 2023-09-14 12:25:38.422197+02
 2023-09-14 12:20:38.422197+02 | 2023-09-14 12:26:38.422197+02 | 2023-09-14 12:26:38.422197+02 | 2023-09-14 12:26:38.422197+02

It does not figure with me why you should want to create a function for that ...

VValdar · September 2023

I got one more expression I like to use as well, it is to multiply your input by the interval 1 minute:

with cte_input (mi) as
(
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6
)
  select mi
       , current_timestamp(0) + mi * interval '1 minute' as new_time
    from cte_input
order by 1;

mi  new_time           
--  -------------------
 1  2023-09-14 12:30:29
 2  2023-09-14 12:31:29
 3  2023-09-14 12:32:29
 4  2023-09-14 12:33:29
 5  2023-09-14 12:34:29
 6  2023-09-14 12:35:29

declare a variable inside a function

Best Answer

Answers

Leave a Comment