SQL Script - convert prev/next_fire_time to DD/MM/YYYY HH:mm:ss format

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

SQL Script - convert prev/next_fire_time to DD/MM/YYYY HH:mm:ss format

popianovka
Hi all,

I've written a simple pl/sql script (function) to convert milliseconds to DD/MM/YYYY HH:mm:ss format. Quartz stores milliseconds in a NUMBE field so it is hard to use date/time functions on these values.  I've tested in toad and sqlplus and it all works.

usage:

- save and execute script. this creates a function called utc_to_date
- to execute run as part of select statement e.g:
- [b]select utc_to_date(next_fire_time) from sys_qt_triggers where job_name = 'jobX';[/b]

script:

CREATE OR REPLACE FUNCTION utc_to_date (i_utc_dt IN NUMBER)
   RETURN DATE
IS
   o_date        DATE;
   v_utc_start   DATE;
   v_mils        NUMBER := 1000;
   v_secs        NUMBER := 60;
   v_mins        NUMBER := 60;
   v_hrs         NUMBER := 24;
BEGIN
   SELECT TO_DATE ('01/01/1970:00:00:00', 'DD/MM/YY:HH24:MI:SS')
     INTO v_utc_start
     FROM DUAL;

   SELECT v_utc_start + ((((i_utc_dt / v_mils) / v_secs) / v_mins) / v_hrs)
     INTO o_date
     FROM DUAL;

   RETURN o_date;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('UTC_TO_DATE: ERROR');
      DBMS_OUTPUT.put_line (SQLERRM);
END utc_to_date;
/

---------------------------------------------------------------------
Posted via Jive Forums
http://forums.opensymphony.com/thread.jspa?threadID=9876&messageID=20975#20975


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]