Difference between revisions of "Time-based Stored Queries"
From SDU
m |
m (Reverted edits by Agegeleruvy (Talk); changed back to last version by Kowy) |
||
| (12 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| + | __NOTOC__ | ||
| + | [[Category:Customizations]] | ||
| + | {{Global Header}} | ||
| + | {{Global Announcement}} | ||
| + | |||
| + | == Details == | ||
Time-Base Queries are used in [[Stored_Queries|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''. | Time-Base Queries are used in [[Stored_Queries|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''. | ||
| Line 6: | Line 12: | ||
<source lang="javascript">EndAtTime (timespan-name)</source> | <source lang="javascript">EndAtTime (timespan-name)</source> | ||
returns end of the timespan's period. | returns end of the timespan's period. | ||
| + | |||
| + | ''' Caution: Time-based stored queries recalculate timespans only when ''Trigger Time Milestone'' occured. ''' | ||
== Timespans == | == Timespans == | ||
| − | Timespans are simple string values (so must be enclosed in simple quotes leaded by backslash). | + | 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). | ||
| − | Here are | + | === 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. | ||
{| style="width:100%;min-width:800px;vertical-align:top;" | {| style="width:100%;min-width:800px;vertical-align:top;" | ||
! | Name || StartAtTime || EndAtTime | ! | Name || StartAtTime || EndAtTime | ||
| − | ! style="width: | + | ! style="width:450px" | Comment |
|- | |- | ||
| − | | AFTER_NOON || | + | | AFTER_NOON || 26.6.2008 12:00 || 27.6.2008 00:00 || |
|- | |- | ||
| − | | BEFORE_NOON || | + | | BEFORE_NOON || 26.6.2008 00:00 || 26.6.2008 12:00 || |
|- | |- | ||
| − | | LAST_HOUR || | + | | LAST_HOUR || 26.6.2008 10:07 || 26.6.2008 11:07 || |
|- | |- | ||
| − | | LAST_MONTH || | + | | LAST_MONTH || 1.5.2008 00:00 || 1.6.2008 00:00 || full previous month |
|- | |- | ||
| − | | LAST_YEAR || | + | | LAST_YEAR || 1.1.2007 00:00 || 1.1.2008 00:00 || full previous year |
|- | |- | ||
| − | | PAST_HOUR || 26.6.2008 10:07 | + | | PAST_HOUR || 26.6.2008 10:07 || 26.6.2008 11:07 || Current time -1 hour |
|- | |- | ||
| − | | PAST_DAY || 25.6.2008 11:07 | + | | PAST_DAY || 25.6.2008 11:07 || 26.6.2008 11:07 || Current time -1 day |
|- | |- | ||
| − | | PAST_WEEK || 19.6.2008 11:07 | + | | PAST_WEEK || 19.6.2008 11:07 || 26.6.2008 11:07 || Current time -7 days |
|- | |- | ||
| − | | PAST_MONTH || 26.5.2008 11:07 | + | | PAST_MONTH || 26.5.2008 11:07 || 26.6.2008 11:07 || Current time -30 days |
|- | |- | ||
| − | | PAST_YEAR || 26.6.2007 11:07 | + | | PAST_YEAR || 26.6.2007 11:07 || 26.6.2008 11:07 || Current time -1 year |
|- | |- | ||
| − | | | + | | THIS_HOUR || 26.6.2008 11:00 || 26.6.2008 11:07 || |
|- | |- | ||
| − | | | + | | THIS_MONTH || 1.6.2008 00:00 || 26.6.2008 11:07 || |
|- | |- | ||
| − | | | + | | THIS_MONTH_LAST_YEAR || 1.6.2007 00:00 || 1.7.2007 00:00 || |
|- | |- | ||
| − | | YESTERDAY || | + | | TODAY || 26.6.2008 00:00 || 26.6.2008 11:07 || |
| + | |- | ||
| + | | TODO_TODAY || 26.6.2008 11:07 || 27.6.2008 00:00 || used in TO DO and callback stored queries | ||
| + | |- | ||
| + | | TTV_THRESHOLD || 26.6.2008 11:07 || 27.6.2008 00:00 || Use this to define the query used by the Option 'ttv_highlight'. See the readme for that Option for more information. | ||
| + | |- | ||
| + | | YESTERDAY || 25.6.2008 00:00 || 26.6.2008 00:00 || | ||
|} | |} | ||
| + | == See Also == | ||
| + | [[Stored Queries]] | ||
| − | + | Unicenter Service Desk Modification Guide page 70. | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
Latest revision as of 05:23, 30 November 2010
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.
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: <source lang="javascript">StartAtTime (timespan-name)</source> returns begin of the timespan's period. <source lang="javascript">EndAtTime (timespan-name)</source> 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.
| Name | StartAtTime | EndAtTime | Comment |
|---|---|---|---|
| AFTER_NOON | 26.6.2008 12:00 | 27.6.2008 00:00 | |
| BEFORE_NOON | 26.6.2008 00:00 | 26.6.2008 12:00 | |
| LAST_HOUR | 26.6.2008 10:07 | 26.6.2008 11:07 | |
| LAST_MONTH | 1.5.2008 00:00 | 1.6.2008 00:00 | full previous month |
| LAST_YEAR | 1.1.2007 00:00 | 1.1.2008 00:00 | full previous year |
| PAST_HOUR | 26.6.2008 10:07 | 26.6.2008 11:07 | Current time -1 hour |
| PAST_DAY | 25.6.2008 11:07 | 26.6.2008 11:07 | Current time -1 day |
| PAST_WEEK | 19.6.2008 11:07 | 26.6.2008 11:07 | Current time -7 days |
| PAST_MONTH | 26.5.2008 11:07 | 26.6.2008 11:07 | Current time -30 days |
| PAST_YEAR | 26.6.2007 11:07 | 26.6.2008 11:07 | Current time -1 year |
| THIS_HOUR | 26.6.2008 11:00 | 26.6.2008 11:07 | |
| THIS_MONTH | 1.6.2008 00:00 | 26.6.2008 11:07 | |
| THIS_MONTH_LAST_YEAR | 1.6.2007 00:00 | 1.7.2007 00:00 | |
| TODAY | 26.6.2008 00:00 | 26.6.2008 11:07 | |
| TODO_TODAY | 26.6.2008 11:07 | 27.6.2008 00:00 | used in TO DO and callback stored queries |
| TTV_THRESHOLD | 26.6.2008 11:07 | 27.6.2008 00:00 | Use this to define the query used by the Option 'ttv_highlight'. See the readme for that Option for more information. |
| YESTERDAY | 25.6.2008 00:00 | 26.6.2008 00:00 |
See Also
Unicenter Service Desk Modification Guide page 70.
