The Vertica Database Forums

Posted: **Tue Nov 24, 2015 3:03 pm**

Hi Experts,

I am looking for an equivalent function in Vertica which is similar to NUMTODSINTERVAL function in ORACLE 11g. Could someone please let me know about this?

Regards,
Sreekanth

Posted: **Mon Dec 28, 2015 6:06 am**

Hi,

You can try creating your own NUMTODSINTERVAL function in Vertica.

Maybe something like this:

Code: Select all

CREATE OR REPLACE FUNCTION numtodsinterval(x NUMERIC, y VARCHAR) RETURN VARCHAR
AS BEGIN 
RETURN
  CASE
    WHEN UPPER(y) = 'DAY' THEN TO_CHAR(TO_CHAR((TRUNC(sysdate) + (TO_CHAR(x) || ' DAY')::interval)::timestamp - TRUNC(sysdate), 'DD')::INT, 'FMSG000000009') || TO_CHAR((TRUNC(sysdate) + (TO_CHAR(x) || ' DAY')::interval)::timestamp - TRUNC(sysdate), ' HH24:MI:SS.US')
    WHEN UPPER(y) = 'HOUR' THEN TO_CHAR(TO_CHAR((TRUNC(sysdate) + (TO_CHAR(x) || ' HOUR')::interval)::timestamp - TRUNC(sysdate), 'DDD')::INT, 'FMSG000000009') || TO_CHAR((TRUNC(sysdate) + (TO_CHAR(x) || ' HOUR')::interval)::timestamp - TRUNC(sysdate), ' HH24:MI:SS.US')
    WHEN UPPER(y) = 'MINUTE' THEN TO_CHAR(TO_CHAR((TRUNC(sysdate) + (TO_CHAR(x) || ' MINUTE')::interval)::timestamp - TRUNC(sysdate), 'DD')::INT, 'FMSG000000009') || TO_CHAR((TRUNC(sysdate) + (TO_CHAR(x) || ' MINUTE')::interval)::timestamp - TRUNC(sysdate), ' HH24:MI:SS.US')
    WHEN UPPER(y) = 'SECOND' THEN TO_CHAR(TO_CHAR((TRUNC(sysdate) + (TO_CHAR(x) || ' SECOND')::interval)::timestamp - TRUNC(sysdate), 'DD')::INT, 'FMSG000000009') || TO_CHAR((TRUNC(sysdate) + (TO_CHAR(x) || ' SECOND')::interval)::timestamp - TRUNC(sysdate), ' HH24:MI:SS.US')
    ELSE NULL
  END;
END;

Here are some examples of running the NUMTODSINTERVAL function in Oracle:

Code: Select all

SQL> SELECT NUMTODSINTERVAL(1,'DAY') FROM DUAL;
NUMTODSINTERVAL(1,'DAY')
---------------------------------------------------------------------
+000000001 00:00:00.000000000

SQL> SELECT NUMTODSINTERVAL(1,'HOUR') FROM DUAL;
NUMTODSINTERVAL(1,'HOUR')
---------------------------------------------------------------------
+000000000 01:00:00.000000000

SQL> SELECT NUMTODSINTERVAL(1,'MINUTE') FROM DUAL;
NUMTODSINTERVAL(1,'MINUTE')
---------------------------------------------------------------------
+000000000 00:01:00.000000000

SQL> SELECT NUMTODSINTERVAL(1,'SECOND') FROM DUAL;
NUMTODSINTERVAL(1,'SECOND')
---------------------------------------------------------------------
+000000000 00:00:01.000000000

SQL> SELECT NUMTODSINTERVAL(1000,'HOUR') FROM DUAL;
NUMTODSINTERVAL(1,'SECOND')
---------------------------------------------------------------------
+000000041 16:00:00.000000000

After creating the new function in Vertica, here are the same queries executed in Vertca:

Code: Select all

dbadmin=> SELECT NUMTODSINTERVAL(1,'DAY');
      NUMTODSINTERVAL
----------------------------
 +000000001 00:00:00.000000
(1 row)

dbadmin=> SELECT NUMTODSINTERVAL(1,'HOUR');
      NUMTODSINTERVAL
----------------------------
 +000000000 01:00:00.000000
(1 row)

dbadmin=> SELECT NUMTODSINTERVAL(1,'MINUTE');
      NUMTODSINTERVAL
----------------------------
 +000000000 00:01:00.000000
(1 row)

dbadmin=> SELECT NUMTODSINTERVAL(1,'SECOND');
      NUMTODSINTERVAL
----------------------------
 +000000000 00:00:01.000000
(1 row)

dbadmin=> SELECT NUMTODSINTERVAL(1000,'HOUR');
      NUMTODSINTERVAL
----------------------------
 +000000041 16:00:00.000000
(1 row)

You may have to play with the formatting a bit, but I hope this helps!

The Vertica Database Forums

NUMTODSINTERVAL equivalent function in Vertica.

NUMTODSINTERVAL equivalent function in Vertica.

Re: NUMTODSINTERVAL equivalent function in Vertica.