ServiceDesk Plus Query for Worklogs

ServiceDesk Plus Query for Worklogs

Hi all, 

I trying to run a query which I got from the SDP Support team on Analytics. 

In the query there are static time values used like this: 

  1. CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-01-01 00:00:00')
I'm looking for a way to have dynamic time value. So that I can import the new values and the changed values directly to Zoho Analytics. 

Here is the whole query: (static timestamps marked in red) 

  1. SELECT rctd.FIRST_NAME "Technician",ct.chargeid "Worklog ID",COALESCE(wtk.WORKORDERID , wo.workorderid) "Module ID", to_timestamp((ct.ts_starttime)/1000)::TIMESTAMP "Time Start Time",to_timestamp((ct.ts_endtime)/1000)::TIMESTAMP "Time End Time",to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",'Request' "Module", CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(wo2.TITLE) ELSE MAX(wo.TITLE) END "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",ct.description "Worklog Description", ad.ORG_NAME AS "Account",ct.inc_nonoper_hours "Include Operations Hours",wtd.name "Worklog Type",wof.UDF_CHAR1 "On-Site Visit",wof.UDF_CHAR2 "Error Correction" FROM ChargesTable ct
    LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID
    LEFT JOIN WorkOrder wo ON wotoc.WORKORDERID=wo.WORKORDERID
    LEFT JOIN workorderstates wos ON wos.workorderid=wo.workorderid
    LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
    LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID
    LEFT JOIN WorkorderToTaskDetails wtk ON tk.TASKID=wtk.TASKID
    LEFT JOIN Workorder wo2 ON wtk.WORKORDERID=wo2.WORKORDERID
    LEFT JOIN SDUser rcti ON wos.ownerid=rcti.USERID
    LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
    LEFT JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid
    LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
    LEFT JOIN worklogtypedefinition wtd on wtd.worklogtypeid=ct.worklogtypeid
    LEFT JOIN worklog_fields wof on ct.chargeid=wof.worklogid
    WHERE (wtk.WORKORDERID IS NOT NULL OR wo.workorderid IS NOT NULL) AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-01-01 00:00:00') * 1000 AS BIGINT)  AND 
    ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-03-30 00:00:00') * 1000 AS BIGINT) GROUP BY COALESCE(wtk.WORKORDERID , wo.workorderid), rctd.FIRST_NAME,ct.CREATEDTIME,ad.ORG_NAME,ct.chargeid,wtd.name,wof.UDF_CHAR1,wof.UDF_CHAR2

    UNION
    SELECT rctd.FIRST_NAME "Technician",ct.chargeid "Worklog ID",COALESCE(ptk.PROBLEMID , pb.PROBLEMID) "Module ID",to_timestamp((ct.ts_starttime)/1000)::TIMESTAMP "Time Start Time",to_timestamp((ct.ts_endtime)/1000)::TIMESTAMP "Time End Time",to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",'Problem' "Module", CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(pb2.TITLE) ELSE MAX(pb.TITLE) END "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",ct.description "Worklog Description",ad.ORG_NAME AS "Account",ct.inc_nonoper_hours "Include Operations Hours",wtd.name "Worklog Type",wof.UDF_CHAR1 "On-Site Visit",wof.UDF_CHAR2 "Error Correction"  FROM ChargesTable ct
    LEFT JOIN ProblemToCharge pbtoc ON ct.CHARGEID=pbtoc.CHARGEID
    LEFT JOIN Problem pb ON pbtoc.PROBLEMID=pb.PROBLEMID
    LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
    LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID
    LEFT JOIN ProblemToTaskDetails ptk ON tk.TASKID=ptk.TASKID
    LEFT JOIN Problem pb2 ON ptk.PROBLEMID=pb2.PROBLEMID
    LEFT JOIN SDUser rcti ON pb.ownerid=rcti.USERID
    LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
    LEFT JOIN AccountSiteMapping asm ON pb.siteid=asm.siteid
    LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
    LEFT JOIN worklogtypedefinition wtd on wtd.worklogtypeid=ct.worklogtypeid
    LEFT JOIN worklog_fields wof on ct.chargeid=wof.worklogid
    WHERE (ptk.PROBLEMID IS NOT NULL OR pb.PROBLEMID IS NOT NULL) AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-01-01 00:00:00') * 1000 AS BIGINT)  AND 
    ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-03-30 00:00:00') * 1000 AS BIGINT)  GROUP BY COALESCE(ptk.PROBLEMID , pb.PROBLEMID), rctd.FIRST_NAME,ct.CREATEDTIME,ad.ORG_NAME,ct.chargeid,wtd.name,wof.UDF_CHAR1,wof.UDF_CHAR2

    UNION
    SELECT rctd.FIRST_NAME "Technician",ct.chargeid "Worklog ID",COALESCE(ctk.CHANGEID , ch.CHANGEID) "Module ID",to_timestamp((ct.ts_starttime)/1000)::TIMESTAMP "Time Start Time",to_timestamp((ct.ts_endtime)/1000)::TIMESTAMP "Time End Time", to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",'Change' "Module",CASE WHEN MAX(tk.TASKID) IS NOT NULL THEN MAX(ch2.TITLE) ELSE MAX(ch.TITLE) END "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",ct.description "Worklog Description",ad.ORG_NAME AS "Account",ct.inc_nonoper_hours "Include Operations Hours",wtd.name "Worklog Type",wof.UDF_CHAR1 "On-Site Visit",wof.UDF_CHAR2 "Error Correction"  FROM ChargesTable ct
    LEFT JOIN ChangeToCharge chtoc ON ct.CHARGEID=chtoc.CHARGEID
    LEFT JOIN ChangeDetails ch ON chtoc.CHANGEID=ch.CHANGEID
    LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
    LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID
    LEFT JOIN ChangeToTaskDetails ctk ON tk.TASKID=ctk.TASKID
    LEFT JOIN ChangeDetails ch2 ON ctk.CHANGEID=ch2.CHANGEID
    LEFT JOIN SDUser rcti ON ch.technicianid=rcti.USERID
    LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
    LEFT JOIN AccountSiteMapping asm ON ch.siteid=asm.siteid
    LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
    LEFT JOIN worklogtypedefinition wtd on wtd.worklogtypeid=ct.worklogtypeid
    LEFT JOIN worklog_fields wof on ct.chargeid=wof.worklogid
    WHERE (ctk.CHANGEID IS NOT NULL OR ch.CHANGEID IS NOT NULL) AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-01-01 00:00:00') * 1000 AS BIGINT)  AND 
    ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-03-30 00:00:00') * 1000 AS BIGINT)  GROUP BY COALESCE(ctk.CHANGEID , ch.CHANGEID), rctd.FIRST_NAME,ct.CREATEDTIME,ad.ORG_NAME,ct.chargeid,wtd.name,wof.UDF_CHAR1,wof.UDF_CHAR2

    UNION
    SELECT rctd.FIRST_NAME "Technician",ct.chargeid "Worklog ID",tpr.PROJECTID "Module ID",to_timestamp((ct.ts_starttime)/1000)::TIMESTAMP "Time Start Time",to_timestamp((ct.ts_endtime)/1000)::TIMESTAMP "Time End Time",to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",'Project' "Module" , MAX(pr.TITLE) "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600
    "Time Spent",ct.description "Worklog Description",ad.ORG_NAME AS "Account",ct.inc_nonoper_hours "Include Operations Hours",wtd.name "Worklog Type",wof.UDF_CHAR1 "On-Site Visit",wof.UDF_CHAR2 "Error Correction"  FROM ChargesTable ct
    LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
    LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID
    LEFT JOIN TaskTOProjects tpr ON tk.taskid=tpr.taskid
    LEFT JOIN ProjectDetails pr ON tpr.PROJECTID=pr.PROJECTID
    LEFT JOIN SDUser rcti ON pr.ownerid=rcti.USERID
    LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
    LEFT JOIN ProjectAccMapping pam ON pr.projectid=pam.projectid
    LEFT JOIN AccountDefinition ad ON pam.accountid=ad.org_id
    LEFT JOIN worklogtypedefinition wtd on wtd.worklogtypeid=ct.worklogtypeid
    LEFT JOIN worklog_fields wof on ct.chargeid=wof.worklogid
    WHERE tk.MODULE IN ('project', 'milestone') AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-01-01 00:00:00') * 1000 AS BIGINT)  AND 
    ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-03-30 00:00:00') * 1000 AS BIGINT)  GROUP BY tpr.PROJECTID,rctd.FIRST_NAME,ct.CREATEDTIME,ad.ORG_NAME,ct.chargeid,wtd.name,wof.UDF_CHAR1,wof.UDF_CHAR2

    UNION
    SELECT rctd.FIRST_NAME "Technician",ct.chargeid "Worklog ID",tk.TASKID "Module ID",to_timestamp((ct.ts_starttime)/1000)::TIMESTAMP "Time Start Time",to_timestamp((ct.ts_endtime)/1000)::TIMESTAMP "Time End Time", to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",'General' "Module" ,MAX(tk.TITLE) "Title", CAST(SUM(ct.TIMESPENT) AS FLOAT)/1000/3600 "Time Spent",ct.description "Worklog Description",ad.ORG_NAME AS "Account",ct.inc_nonoper_hours "Include Operations Hours",wtd.name "Worklog Type",wof.UDF_CHAR1 "On-Site Visit",wof.UDF_CHAR2 "Error Correction"  FROM ChargesTable ct
    LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
    LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID
    LEFT JOIN SDUser rcti ON tk.ownerid=rcti.USERID
    LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
    LEFT JOIN sitedefinition sdef on sdef.siteid=tk.siteid
    LEFT JOIN AccountSiteMapping asm ON sdef.siteid=asm.siteid
    LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
    LEFT JOIN worklogtypedefinition wtd on wtd.worklogtypeid=ct.worklogtypeid
    LEFT JOIN worklog_fields wof on ct.chargeid=wof.worklogid
    where module='general' AND
    ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-01-01 00:00:00') * 1000 AS BIGINT)  AND 
    ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-03-30 00:00:00') * 1000 AS BIGINT)   GROUP BY tk.TASKID,rctd.FIRST_NAME,ct.CREATEDTIME,ad.ORG_NAME,ct.chargeid,wtd.name,wof.UDF_CHAR1,wof.UDF_CHAR2 ORDER BY 5
Can someone help me to modify these query, so that I can run it from Zoho Analytics, without having to adjust the time value each time?

Best Regards 
Anish