Query to show tickets created based on shift time_PGSQL

Query to show tickets created based on shift time_PGSQL

Filter mentioned by converting into minutes.

PGSQL:

7.30 AM - 7.30 PM

SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status",
LONGTODATE(wo.CREATEDTIME) AS "Created Time",
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "hh",
extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "minu",extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 +  extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "total minutes"  FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE wo.ISPARENT='1'  and
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 +  extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP)  > 558
and extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 +  extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) < 1038

7.30 PM - 7.30 AM

SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status",
LONGTODATE(wo.CREATEDTIME) AS "Created Time",
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "hh",
extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "minu",extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 +  extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "total minutes"  FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE wo.ISPARENT='1'  and
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 +  extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP)  > 1158 or extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 +  extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) < 438

6 AM - 10 PM

SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status",
LONGTODATE(wo.CREATEDTIME) AS "Created Time",
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "hh",
extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "minu",extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 +  extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "total minutes"  FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE wo.ISPARENT='1'  and
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 +  extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP)  > 360
and extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 +  extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) < 1320

 10 PM - 6 AM

SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status",
LONGTODATE(wo.CREATEDTIME) AS "Created Time",
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "hh",
extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "minu",extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 +  extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "total minutes"  FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE wo.ISPARENT='1'  and
extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 +  extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP)  > 1320
and extract(hour from to_timestamp(wo.createdtime/1000)::TIMESTAMP)*60 +  extract(minute from to_timestamp(wo.createdtime/1000)::TIMESTAMP) < 360

We have to calculate minutes in 24 hour format.

Example:  

For 6 AM:  00:00 - 06:00=> 6 Hours (6*60 minutes)= 360 minutes.

For 10 PM: 00:00 - 22:00=> 22 Hours (22*60 Minutes)= 1320 Minutes​


      New to ADManager Plus?

        New to ADSelfService Plus?

        Start your free trial

          Resources

              • Related Articles

              • Query to show technician created time

                PGSQL & MSSQL: SELECT AaaUser.FIRST_NAME "Technician Name", aci.emailid "Email ID", LONGTODATE(AaaUser.createdtime)  "Technician Created Time", SDUSER.STATUS "Status(Active/Inactive)" FROM AaaUser left JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID ...

              • Query to show tickets created and resolved during weekdays and weekends_ PGSQL

                1. Mon-Sat 7 PM - 6 AM: a. Created tickets: SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", aau.FIRST_NAME AS "Requester", dpt.DEPTNAME AS "Department", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS ...

              • Query to show Count of tickets based on OP Hrs_ MSSQL

                MSSQL: OP Hrs: (8.30 - 18.30) SELECT  qd.queuename "Group", "pd"."PRIORITYNAME" AS "Priority", ad.org_name "Account", count(wo.workorderid) "Total Tickets"  FROM WorkOrder wo LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN ...

              • Query to show Total Onhold time_PGSQL

                PGSQL: SELECT LONGTODATE(wo.CREATEDTIME) AS "Created Time", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", cd.CATEGORYNAME AS "Category", ...

              • Query to show total time spent of a technician for the current month_PGSQL

                Query show total time spent by technician for the current month regardless of the ticket created date. PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS ...

                Related Products