4. Date Filter

4. Date Filter

Custom Date Filter Format:

PGSQL

createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP ' 2019-01-01 00:00:00') * 1000 AS BIGINT)  AND
createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP ' 2019-01-30 00:00:00') * 1000 AS BIGINT) 

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician",
       ((wo.TIMESPENTONREQ)/1000/3600) "Time elapsed" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1')
  AND wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2019-01-01 00:00:00') * 1000 AS BIGINT)
  AND wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2019-01-30 00:00:00') * 1000 AS BIGINT)
ORDER BY 5

MSSQL

dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ( createdtime/1000),'1970-01-01 00:00:00') >= convert(varchar,' 2019-01-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ( createdtime/1000),'1970-01-01 00:00:00') <= convert(varchar,' 2019-01-30 23:59',21) 

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician",
       ((wo.TIMESPENTONREQ)/1000/3600) "Time elapsed" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1')
  AND dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') >= convert(varchar,'2019-01-01 00:00', 21)
  AND dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') <= convert(varchar,'2019-01-30 23:59', 21)
ORDER BY 5

Using Date Templates

CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician",
       ((wo.TIMESPENTONREQ)/1000/3600) "Time elapsed" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1')
  AND wo.CREATEDTIME >= <from_thisweek>
  AND wo.CREATEDTIME <= <to_thisweek>
ORDER BY 5

Available Date Templates

    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>
    • This year - <from_thisyear> - <to_thisyear>
    • Last year - <from_lastyear> - <to_lastyear>

PGSQL:

Last 7 days :

to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) - '7 days'::interval,'yyyy-mm-dd 00:00:00'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS')

Last 15 days :

to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) - '15 days'::interval,'yyyy-mm-dd 00:00:00'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS')

Last 30 days:

to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) - '30 days'::interval,'yyyy-mm-dd 00:00:00'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS')

Next 7 days :

to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) + '7 days'::interval,'yyyy-mm-dd 23:59:59'),'YYYY-MM-DD HH24:MI:SS')

Next 15 days :

to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) + '15 days'::interval,'yyyy-mm-dd 23:59:59'),'YYYY-MM-DD HH24:MI:SS')

Next 30 days :

to_timestamp(wo.createdtime/1000) >= to_timestamp(to_char(CURRENT_DATE,'yyyy-mm-dd hh:mm:ss'),'YYYY-MM-DD HH24:MI:SS') and to_timestamp(wo.createdtime/1000) <= to_timestamp(to_char(date_trunc('day', CURRENT_DATE) + '30 days'::interval,'yyyy-mm-dd 23:59:59'),'YYYY-MM-DD HH24:MI:SS')


MSSQL:

Last 7 days :

dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') >= DATEADD(day, DATEDIFF(day, 0, GETDATE())-7, 0) and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') <= GETDATE()

Last 15 days :

dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') >= DATEADD(day, DATEDIFF(day, 0, GETDATE())-15, 0) and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') <= GETDATE()

Last 30 days :

dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') >= DATEADD(day, DATEDIFF(day, 0, GETDATE())-30, 0) and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') <= GETDATE()

Next 7 days :

dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') >= GETDATE() and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') <= DATEADD(s,-1,DATEADD(day, DATEDIFF(day,0,GETDATE())+7,1))

Next 15 days :

dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') >= GETDATE() and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') <= DATEADD(s,-1,DATEADD(day, DATEDIFF(day,0,GETDATE())+15,1))

Next 30 days :

dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo. createdtime/1000), '1970-01-01 00:00:00') >= GETDATE() and dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.createdtime/1000), '1970-01-01 00:00:00') <= DATEADD(s,-1,DATEADD(day, DATEDIFF(day,0,GETDATE())+30,1))


AGING Days


PGSQL Syntax

extract(epoch from(now()::TIMESTAMP -  to_timestamp( wo.createdtime/1000)::TIMESTAMP))/3600/24

Sample query:

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1')
  AND std.statusname = 'open'
  AND extract(epoch FROM(now()::TIMESTAMP - to_timestamp(wo.createdtime/1000)::TIMESTAMP))/3600/24 > 7

MSSQL  Syntax

DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +( wo.createdtime/1000),'1970-01-01 00:00:00'),GETDATE())

Sample query:

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1')
  AND std.statusname = 'open'
  AND DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.createdtime/1000),'1970-01-01 00:00:00'),GETDATE()) > 7


Time Elapsed in HOURS


To convert in hours.

((wo.TIMESPENTONREQ))/1000/3600) 

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status",
       ti.FIRST_NAME AS "Technician",
       ((wo.TIMESPENTONREQ)/1000/3600) "Time elapsed" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1')
ORDER BY 5


                New to ADManager Plus?

                  New to ADSelfService Plus?

                    • Related Articles

                    • Report on Technician Leaves with date filter

                      DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 10000 SELECT cre.FIRST_NAME "Created By", LONGTODATE(TechUnavailability.CREATEDDATE) "Created Date", avail.FIRST_NAME "Technician on Leave", back.FIRST_NAME "Backup Technician", ...
                    • Query to fetch Task Worklog and related entity ID

                      DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 9400 SELECT taskdet.TASKID AS "Task ID", taskdet.TITLE AS "Title", taskowner.FIRST_NAME AS "Owner", taskdet.MODULE AS "Module", wotask.WORKORDERID AS "Request ID", taskprob.PROBLEMID ...
                    • Jar to save criteria in Junk Notification Filter (Admin->Notification Rules)

                      Problem:       Unable to save criteria in Junk Notification Filter ( Admin -> Notification Rules ) Jar is For?       The attached fix fjar is to save criteria in Junk Notification Filter. Applicable SDP version :       10502
                    • 1. Query Basics

                      Basic Query: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status" FROM WorkOrder wo LEFT JOIN SDUser sdu ON ...
                    • Set due by time in a request with value from a date time additional field - Deluge

                      This custom function script is used to set the due by date in the request based on the value that is set in a date additional field.  This is performed using APIs for updating a request. UseCase:  Request due by time will be set based on Incident / ...