Query to show tickets created based on shift time ( PGSQL )

Query to show tickets created based on shift time ( PGSQL )

Tested in build PGSQL (14300)
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 M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Query to show technician created time (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) 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 ...

                    • Query to show Average response time for Category (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...

                    • Query to show tickets created and resolved during weekdays and weekends-PGSQL

                      Working on Builds: 14500 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 ...

                    • Query to show total time spent of a technician for the current month-PGSQL

                      Working on Build's: 14500 Query show total time spent by technician for the current month regardless of the ticket created date based on worklogs added PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS ...

                    • Query to show Total Onhold time ( PGSQL )

                      Tested in build PGSQL (14300) 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", ...

                      Related Products