NUMTODSINTERVAL equivalent function in Vertica.

Moderator: NorbertKrupa

Post Reply
ksreekanth
Newbie
Newbie
Posts: 3
Joined: Tue Nov 24, 2015 2:57 pm

NUMTODSINTERVAL equivalent function in Vertica.

Post by ksreekanth » 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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: NUMTODSINTERVAL equivalent function in Vertica.

Post by JimKnicely » 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!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL Functions”