declare a variable inside a function

Answers

• marcothesane - Select Field - Administrator

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

  1
• VValdar Vertica Employee Employee

  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
  

  0