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
NUMTODSINTERVAL equivalent function in Vertica.
Moderator: NorbertKrupa
-
- Newbie
- Posts: 3
- Joined: Tue Nov 24, 2015 2:57 pm
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: NUMTODSINTERVAL equivalent function in Vertica.
Hi,
You can try creating your own NUMTODSINTERVAL function in Vertica.
Maybe something like this:
Here are some examples of running the NUMTODSINTERVAL function in Oracle:
After creating the new function in Vertica, here are the same queries executed in Vertca:
You may have to play with the formatting a bit, but I hope this helps!
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;
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
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)
Jim Knicely
![Image](http://vertica-forums.com/MyUploads/ASE_ebadge.png)
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
![Image](http://vertica-forums.com/MyUploads/ASE_ebadge.png)
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.