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