TMACUL

http://www.servicedeskusers.com/Time-based_Stored_Queries

Blog Post created by TMACUL Champion on Jan 1, 2016

Time-based Stored Queries

 

From SDU

Jump to: navigation, search

 

To make corrections or additions to this article, select the edit tab above.
To discuss or ask questions about this article, select the discussion tab above.


 

Details

Time-Base Queries are used in Stored Queries to make DateTime WHERE clauses possible. This is partly described in Unicenter Service Desk Modification Guide in the chapter 4. Customization of Queries and Messages.

You can use an exact date in the Time-Based Stored Queries or counted time using one of these 2 functions:

StartAtTime (timespan-name)

returns begin of the timespan's period.

EndAtTime (timespan-name)

returns end of the timespan's period.

Caution: Time-based stored queries recalculate timespans only when Trigger Time Milestone occured.

Timespans

Timespans are simple string values (so must be enclosed in simple quotes leaded by backslash). They are defined in a special enumeration table, which can be out-of-the-box found in Administration tab -> Service Desk -> Application Data -> Codes -> Timespans.

Each timespan has 3 milestones which are connected to current time:

  • Start Time - beginning of the timespan period
  • End Time - end of the timespan period
  • Trigger Time - this milestone guides how often is time value recalculated in a stored query

You can define exact values (ie. 2008 for year, 4 for month, 3 for day and so on) into the time parts or relative differences against current value (ie. +1 or -5 and so on).

Out-of-the-box timespans

Here are list of predefined timespan names, but you can create new ones if you need. Examples assume actual date and time is 26.6.2008 11:07.

NameStartAtTimeEndAtTimeComment
AFTER_NOON26.6.2008 12:0027.6.2008 00:00
BEFORE_NOON26.6.2008 00:0026.6.2008 12:00
LAST_HOUR26.6.2008 10:0726.6.2008 11:07
LAST_MONTH1.5.2008 00:001.6.2008 00:00full previous month
LAST_YEAR1.1.2007 00:001.1.2008 00:00full previous year
PAST_HOUR26.6.2008 10:0726.6.2008 11:07Current time -1 hour
PAST_DAY25.6.2008 11:0726.6.2008 11:07Current time -1 day
PAST_WEEK19.6.2008 11:0726.6.2008 11:07Current time -7 days
PAST_MONTH26.5.2008 11:0726.6.2008 11:07Current time -30 days
PAST_YEAR26.6.2007 11:0726.6.2008 11:07Current time -1 year
THIS_HOUR26.6.2008 11:0026.6.2008 11:07
THIS_MONTH1.6.2008 00:0026.6.2008 11:07
THIS_MONTH_LAST_YEAR1.6.2007 00:001.7.2007 00:00
TODAY26.6.2008 00:0026.6.2008 11:07
TODO_TODAY26.6.2008 11:0727.6.2008 00:00used in TO DO and callback stored queries
TTV_THRESHOLD26.6.2008 11:0727.6.2008 00:00Use this to define the query used by the Option 'ttv_highlight'. See the readme for that Option for more information.
YESTERDAY25.6.2008 00:0026.6.2008 00:00

See Also

Stored Queries

Unicenter Service Desk Modification Guide page 70.

Retrieved from "http://www.servicedeskusers.com/Time-based_Stored_Queries"

 

Summary CA SDM Scoreboard 

Outcomes