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:

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)

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