PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 07/15/2015 9:36 AM by  Greg Moeller
How 2 Convert Quartz Scheduler date/times?
 8 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Milo Tsukroff
Private
Private
Veteran Member
(117 points)
Veteran Member
Posts:43


Send Message:

--
07/29/2008 5:07 PM

    I'm writing some Crystal reports that hit the LBI table.  I want to know when reports are scheduled to run, and when they ran, etc.  I can build performance metrics against this once I can get this info.  Apparently the Quartz Scheduler stores date/time as a number using its own Java method.  The fields in question are in EJS_TRIGGERS -- NEXT_FIRE_TIME, PREV_FIRE_TIME, START_TIME, etc. 

    Does anyone know the algorithm to use to convert these times?  I see big massive numbers instead of anything sensible.  I want a Year-Month-Day HH-MM-SS:xxxx  result.  There must be some formula.

    Halp!!! 

    Chris Martin
    Private
    Private
    Veteran Member
    (825 points)
    Veteran Member
    Posts:277


    Send Message:

    --
    07/29/2008 9:49 PM
    If I recall correctly, it's the number of milliseconds since the Unix base of Thursday 1970-01-01 00:00:00.000 GMT.
    Milo Tsukroff
    Private
    Private
    Veteran Member
    (117 points)
    Veteran Member
    Posts:43


    Send Message:

    --
    07/31/2008 1:19 PM

    Thanks, Chris!  That's what I needed.  The times are stored in UNIX timestamp format, times 1,000 to give the millisecond.  I used this formula for the PREV_FIRE_TIME field:

    DateAdd ( "s", (INT({EJS_TRIGGERS.PREV_FIRE_TIME}/1000)-14400), #01/01/1970# )
    // 14400 = seconds for 4 hours - lag behind UCT for EDT

    There's an additional compensation for the time zone, because the times are stored in Universal Coordinated Time.

    Chris Martin
    Private
    Private
    Veteran Member
    (825 points)
    Veteran Member
    Posts:277


    Send Message:

    --
    08/03/2008 6:14 PM

    Glad to hear it helped.   Thanks for posting the specifics of what worked (as you always do).

    clb393
    Financial Data Specialist
    SoNH
    Basic Member
    (44 points)
    Basic Member
    Posts:24


    Send Message:

    --
    07/15/2015 8:20 AM
    Along these same lines, i'm trying to find out the actual time that the report is scheduled, not the date. Do I get that from the Start_Time field from ejs_triggers, or is it another table? And can you provide the formula for deriving the time?

    Thank you!
    Chris
    Greg Moeller
    Private
    Private
    Veteran Member
    (3873 points)
    Veteran Member
    Posts:1377


    Send Message:

    --
    07/15/2015 8:45 AM
    I found the scheduled time in ejs_cron_triggers.cron_expression.
    You'll have to manipulate the field a little bit, but the hour/minute are there. (along with the days, etc)
    Greg Moeller
    Private
    Private
    Veteran Member
    (3873 points)
    Veteran Member
    Posts:1377


    Send Message:

    --
    07/15/2015 9:01 AM
    We actually created a db view to get this report to work from..
    Create this view (we're Oracle here, so you may have to adjust for SQL)

    CREATE OR REPLACE FORCE VIEW "GHSDEV"."GHS_LBI_RS_SCHEDULES" AS
    select -- selecting fields to create a dynamic listing of scheduled LBI reports
    trim(r.reportname) report_name,
    trim(r.description) report_desc,
    trim(t.trigger_name) schedule_name,
    trim(t.job_name) report_id,
    trim(c.cron_expression) cron_exp,
    trim(r.owner) owner
    from lawrs.ejs_triggers t
    join lawrs.ers_reports r on
    r.reportid = to_number (t.job_name)
    join lawrs.ejs_cron_triggers c on c.trigger_name = t.trigger_name and c.trigger_group = t.trigger_group
    where t.job_name <= '9999' and r.instanceid = '0';

    Then grab the attached report and run..

    Hope this helps,
    -Greg
    Attachments
    clb393
    Financial Data Specialist
    SoNH
    Basic Member
    (44 points)
    Basic Member
    Posts:24


    Send Message:

    --
    07/15/2015 9:02 AM
    Terrific, thanks Greg!

    Oh, and we're working with SQL Developer, so your script works perfectly!
    Greg Moeller
    Private
    Private
    Veteran Member
    (3873 points)
    Veteran Member
    Posts:1377


    Send Message:

    --
    07/15/2015 9:36 AM
    Glad to help!
    You are not authorized to post a reply.